Simple problem importing a file using a macro

  • Thread starter Thread starter Dexter
  • Start date Start date
D

Dexter

Hello Everyone,

I am very new to Excel programming, but I have written a few simple macros
before. However, I have never really worked with loading information out of a
file and then saving it back.

I have a series of text files which contain data that I want to import. I ran
the macro recorder to import one file that I wanted where I wanted it, but I am
now trying to automate the process. All the files end in .PRN. I write the
name of the file that I want in the "A" row of an Excel spreadsheet, minus the
".PRN". The files are always on a floppy disk in the "A:\" drive. The macro
recorded came up with this line (followed by a whole bunch of other stuff) which
is (sorry if the line wrapping is funny):

With ActiveSheet.QueryTables.Add(Connection:="TEXT;A:\EB1114.PRN",
Destination:=Range("A10"))

I select the cell which has the name of the file in it and load it into a
string as follows:

FileNameToImport = ActiveCell.Value

The active cell contains a the text EB1114, for example. I can make it look
just like what follows the "Connection:=" command by adding:

FileNameToImport = "TEXT;A:\" & FileNameToImport & ".PRN"

However, cannot figure out how to use a string with the "Connection" command.
Can anyone help me?

Thanks a lot in advance,
Dexter
 
I think you could just do something like this::

Option Explicit
Sub testme01()

Dim myCell As Range
Dim DestCell As Range
Dim otherWks As Worksheet
Dim curWks As Worksheet

Set otherWks = Worksheets("sheet2")
Set curWks = Worksheets("sheet1")

With curWks
For Each myCell In .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
With otherWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
With .QueryTables.Add(Connection:= _
"TEXT;A:\" & myCell.Value & ".txt", _
Destination:=DestCell)
.FieldNames = True
'Lots of recorded code snipped!
.Refresh BackgroundQuery:=False
End With
End With
Next myCell
End With

End Sub


But when I do these type of things, I usually just record a macro when I do
File|Open. Then if I want it copied somewhere else, I just copy and paste to
its final destination.

I find it a lot simpler.
 
Back
Top