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?
--
Dennis Vlasich
Claremont, CA

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
--
Dennis Vlasich
Claremont, CA


"Dennis Vlasich" wrote:

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?
--
Dennis Vlasich
Claremont, CA

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" wrote in message
...
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
--
Dennis Vlasich
Claremont, CA


"Dennis Vlasich" wrote:

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?
--
Dennis Vlasich
Claremont, CA

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"
wrote in message
news:9B8E9A01-70C5-4111-B10A-
...
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
--
Dennis Vlasich
Claremont, CA


"Dennis Vlasich" wrote:

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?
--
Dennis Vlasich
Claremont, CA


.