How to Apply a Macro to Data Model?

[Jean Huguet]

Background info:

A colleague gave me a macro. When I click it, I choose a txt file and the macro automatically formats the file the way I need in a new txt file.

Many of the files I'm using have over 1 million rows. The macro only formats the first 1 million and leave the rest of the data out of the new txt file it creates.

I found Chandoo's very useful tutorial on how to create a data model in Excel to analyze files that contain more than 1 million rows. I'm able to create a data model following the tutorial instructions.

*****
The problem:

What I don't know now is how to apply my macro to the data model itself in order to generate the new txt file I need.

When I click the macro, the only option is to choose a txt file.

I would greatly appreciate your help. I have little knowledge of macros or data models.

Thank you in advance.

Here's the macro code in case it is relevant to solving the issue:

>>> use code - tags <<<

Option Explicit
Sub ImportTextFile()
'Imports a text file using Excel's own
'(and fast) import function. The example
'assumes that semicolon is used as delimiter.
Dim vFileName

On Error GoTo ErrorHandle

'Here we use a filter to show nothing but text files with
'the extension "*.txt". If you delete the file filter
'("Text Files (*.txt),*.txt")
'all files are shown. There are several other
'possibilities - read the VBA help.
vFileName = Application.GetOpenFilename("Text Files (*.txt),*.txt")

'If the user pressed "Cancel" or didn't select a text file,
'we exit the procedure.
If vFileName = False Or Right(vFileName, 3) <> "txt" Then
   GoTo BeforeExit
End If

'Switch off screen updating for speed.
Application.ScreenUpdating = False

'We now import the selected text file, and data is
'inserted in a new spreadsheet. If you want to use
'another delimiter, you must change "Semicolon:=True"
'to "Semicolon:=False" and set another delimiter
'(e.g. "Tab") to True.
Workbooks.OpenText Filename:=vFileName, _
    Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
    Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2), _
    Array(3, 2), Array(4, 2), Array(5, 9), Array(6, 9), Array(7, 9), Array(8, 9), _
    Array(9, 9), Array(10, 9), Array(11, 9), Array(12, 9), Array(13, 9), Array(14, 9)), _
    TrailingMinusNumbers:=True, Local:=True


'Just to show how we auto adjust the width of column A.
Columns("A:A").EntireColumn.AutoFit

BeforeExit:
Application.ScreenUpdating = True
Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub

 

[p45cal]

The macro seems to be loading data to a new excel sheet but doesn't seem to be saving it at all. Could you tell us what you then do with the spreadsheet; how do you go about saving it?
I see you're importing only the first four columns as text.

This can be done without bringing the data onto a sheet and without the data model but I need some more details. For example if you were to supply a small text file as 'before-import' and another corresponding text file after you've processed it I'm sure I could write you a short (and fast) macro to do the same with no limit on the number of rows.
The files don't have to be big, say less than 100 rows would easily suffice as long as the rows aren't all exactly the same and represent a good cross section of typical rows.

 

[Jean Huguet]

Thank you very much Pascal for your reply. By respect for the time you've put in your reply I just want to mention that an unexpected family issue happened yesterday and I'll have to postpone working on the solution you propose for the time being.