Jean Huguet
New Member
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 <<<
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 <<<
Code:
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
Attachments
Last edited by a moderator: