Workbooks.OpenText method help

[gabejessee]

Hi everyone,

I'm trying import all the text files in a specific folder into excel via macro, and I've ran into a snag.

Here is my macro:

Sub TxtImporter()
Dim f As String, flPath As String
Dim i As Long, j As Long
Dim ws As Worksheet
Dim t As Integer
Application.DisplayAlerts = False
Application.ScreenUpdating = False
flPath = ThisWorkbook.Path & Application.PathSeparator
i = ThisWorkbook.Worksheets.Count
j = Application.Workbooks.Count
f = Dir(flPath & "*.txt")
t = 0
Do Until f = ""
ActiveCell.Offset(rowOffset:=0, columnOffset:=t).Activate
Workbooks.OpenText flPath & f, _
StartRow:=1, DataType:=xlFixedWidth
t = t + 22
i = i + 1
f = Dir
Loop
Application.DisplayAlerts = True
End Sub

The problem is that when I loop around to import the second file cell "A1" is selected despite the ActiveCell.Offset command at the beginning of the loop. ActiveCell puts the cursor into the correct place where I would like to import but then the OpenText command moves it back to "A1" Is the OpenText function limited to only open files from A1? I'm very novice at this so I'm not sure what I need to do to work around this issue. Any help would beappreciated.

Thanks
-G

 

[GS]

Hi everyone,

I'm trying import all the text files in a specific folder into excel
via macro, and I've ran into a snag.

Here is my macro:

Sub TxtImporter()
Dim f As String, flPath As String
Dim i As Long, j As Long
Dim ws As Worksheet
Dim t As Integer
Application.DisplayAlerts = False
Application.ScreenUpdating = False
flPath = ThisWorkbook.Path & Application.PathSeparator
i = ThisWorkbook.Worksheets.Count
j = Application.Workbooks.Count
f = Dir(flPath & "*.txt")
t = 0
Do Until f = ""
ActiveCell.Offset(rowOffset:=0, columnOffset:=t).Activate
Workbooks.OpenText flPath & f, _
StartRow:=1, DataType:=xlFixedWidth
t = t + 22
i = i + 1
f = Dir
Loop
Application.DisplayAlerts = True
End Sub

The problem is that when I loop around to import the second file cell
"A1" is selected despite the ActiveCell.Offset command at the
beginning of the loop. ActiveCell puts the cursor into the correct
place where I would like to import but then the OpenText command
moves it back to "A1" Is the OpenText function limited to only open
files from A1? I'm very novice at this so I'm not sure what I need to
do to work around this issue. Any help would be appreciated.

Thanks
-G

Workbooks.OpenText does that!

Your code suggests you want to arrange each file every 22 cols,
regardless of the number of rows each file contains, on a single sheet.
This means the 1st file starts in cells(1,1), the next in cells(1,22),
the next in cells(1,44), ..and so on. Otherwise, what exactly is your
expectation for this?

DataType xlFixedWidth suggests every file will parse across the same
number of cols.

Not specifying a delimiter suggests the space character is used, AND
the data itself contains no spaces.

Perhaps using standard VBA file I/O functions is a better approach.
This will allow you to read the files into an array and 'dump' the
array into the sheet EXACTLY where you want the data put.

A foreseen problem will be whether the worksheet will support the
number of files, columns-wise! -OR- Do you want each file on a new
sheet and if so, where EXACTLY on the sheet?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Gabriel Jessee]

Thanks for your response Garry. I figured out, via the recorder, that the opentext method isn't the way to do this because open text always opens a new workbook and therefore inserts data in A1 of the new workbook. The ActiveSheet.QueryTables.Add(Connection) function is actually what I need to learnhow to use. So now it is just a matter of learning how to get a loop goingthat will cycle through each of the files in a specific folder.

 

[GS]

Thanks for your response Garry. I figured out, via the recorder, that

the opentext method isn't the way to do this because open text always
opens a new workbook and therefore inserts data in A1 of the new
workbook. The ActiveSheet.QueryTables.Add(Connection) function is
actually what I need to learn how to use. So now it is just a matter
of learning how to get a loop going that will cycle through each of
the files in a specific folder.

Creating QueryTables isn't the way to go either, IMO. Better that you
describe exactly how you want the text file data insered. The rest is
easy!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion