Import text wizard in a macro

[Guest]

I've written a macro to automatically feed the appropriate parameters to the Text Import Wizard to format text data into an active spreadsheet. However, I want the data to be added to the data already in the sheet and the Import Wizards always puts the data in starting at A1. Is there any way to tell it to start putting the data starting at another cell location?

 

[Guest]

After playing around, I sort of answered my own question--but it raised another question. Rather than use the File/Open option (per the other suggestions regarding recording macros for the Import Text wizard), I used Data/Import/Import Data option. It builds a slightly different macro than the File/Open, but when I try to add in the code for the GetFileOpen dialog box and feed the "fileToOpen" variable to the Data/Import version of the code in place of a hard-coded path and filename, it gives me an error when running the macro. Any hints of what I'm doing wrong? Here's the code I'm trying to use (by the way, what is the "With, End With all about?):

fileToOpen = Application.GetOpenFileName("Text Files (*.txt), *.txt")
If fileToOpen <> False Then
Workbooks.Open Filename:=fileToOpen
End If

With ActiveSheet.QueryTables.Add(Connection:=fileToOpen, _
Destination:=ActiveCell)
.Name = "Pay320"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 3, 9, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, _
2, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

 

[Tom Ogilvy]

You don't want to open the file if you are going to import it. Also, you
removed the "Text:" part of the connection string:

fileToOpen = Application.GetOpenFileName("Text Files (*.txt), *.txt")
If fileToOpen <> False Then

With ActiveSheet.QueryTables.Add(Connection:= _
"Text:" & fileToOpen, _
Destination:=ActiveCell)
.Name = "Pay320"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 3, 9, 2, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 2, 2, 2, 2, 2, 2, 2, _
2, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With


instead of doing

ActiveCell.Font.bold = True
ActiveCell.Font.ColorIndex = 3
ActiveCell.Interior.colorIndex = 6

you can do

With ActiveCell
.Font.bold = True
.Font.ColorIndex = 3
.Interior.colorIndex = t
End With

--
Regards,
Tom Ogilvy

After playing around, I sort of answered my own question--but it raised

another question. Rather than use the File/Open option (per the other
suggestions regarding recording macros for the Import Text wizard), I used
Data/Import/Import Data option. It builds a slightly different macro than
the File/Open, but when I try to add in the code for the GetFileOpen dialog
box and feed the "fileToOpen" variable to the Data/Import version of the
code in place of a hard-coded path and filename, it gives me an error when
running the macro. Any hints of what I'm doing wrong? Here's the code I'm
trying to use (by the way, what is the "With, End With all about?):

fileToOpen = Application.GetOpenFileName("Text Files (*.txt), *.txt")
If fileToOpen <> False Then
Workbooks.Open Filename:=fileToOpen
End If

With ActiveSheet.QueryTables.Add(Connection:=fileToOpen, _
Destination:=ActiveCell)
.Name = "Pay320"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 3, 9, 2, 1, 1, 1, 1, 1, 1, 1,

1, 1, 1, 2, 2, 2, 2, 2, 2, 2, _

2, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

the Text Import Wizard to format text data into an active spreadsheet.
However, I want the data to be added to the data already in the sheet and
the Import Wizards always puts the data in starting at A1. Is there any
way to tell it to start putting the data starting at another cell location?

 

[Dennis Vlasich]

Tom,
Thank you for the help. One minor problem (which I
fixed) is that the separator after "Text:" is a semi-
colon, not a colon, so it should have been "Text;". Once
I made that change it worked exactly as I need it to.

-----Original Message-----
You don't want to open the file if you are going to import it. Also, you
removed the "Text:" part of the connection string:

fileToOpen = Application.GetOpenFileName("Text Files (*.txt), *.txt")
If fileToOpen <> False Then

With ActiveSheet.QueryTables.Add(Connection:= _
"Text:" & fileToOpen, _
Destination:=ActiveCell)
.Name = "Pay320"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 3, 9, 2, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 2, 2, 2, 2, 2, 2, 2, _
2, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With


instead of doing

ActiveCell.Font.bold = True
ActiveCell.Font.ColorIndex = 3
ActiveCell.Interior.colorIndex = 6

you can do

With ActiveCell
.Font.bold = True
.Font.ColorIndex = 3
.Interior.colorIndex = t
End With

--
Regards,
Tom Ogilvy


"Dennis Vlasich"

news:9B8E9A01-70C5-4111-B10A-

(e-mail address removed)...

After playing around, I sort of answered my own

question--but it raised
another question. Rather than use the File/Open option (per the other
suggestions regarding recording macros for the Import Text wizard), I used
Data/Import/Import Data option. It builds a slightly different macro than
the File/Open, but when I try to add in the code for the GetFileOpen dialog
box and feed the "fileToOpen" variable to the Data/Import version of the
code in place of a hard-coded path and filename, it gives me an error when
running the macro. Any hints of what I'm doing wrong? Here's the code I'm
trying to use (by the way, what is the "With, End With all about?):

fileToOpen = Application.GetOpenFileName("Text Files (*.txt), *.txt")
If fileToOpen <> False Then
Workbooks.Open Filename:=fileToOpen
End If

With ActiveSheet.QueryTables.Add (Connection:=fileToOpen, _
Destination:=ActiveCell)
.Name = "Pay320"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 3, 9, 2,

1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 2, 2, 2, 2, 2, 2, 2, _

2, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

appropriate parameters to
the Text Import Wizard to format text data into an active spreadsheet.
However, I want the data to be added to the data already in the sheet and
the Import Wizards always puts the data in starting at A1. Is there any
way to tell it to start putting the data starting at another cell location?

.