import data macro, filename specified in cell

[unitedmre]

hi guys

i would like to make a macro for importing a file to my workbook. basically, theres a folder with thousands of *.txt files in it, and i want to be able to write
the name of the file i need into a cell.
the folder is always: c:\import\
and i take "example123.txt" as an example of a file id like to import

with the macro recording function and my smallish vba knowledge, i got so far:


Sub importtxtfile()
'
' importtxtfile Macro
'

Dim FileName As String
FileName = Worksheets("Sheet1").Range("J6").Value

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\import\ &filename" _
, Destination:=Range("$A$12"))
.Name = "filename"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 65001
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll Down:=-9
End Sub


please look at the red code and tell me whats wrong - i just get an error message saying that it can find the file i found a few guides for this task with google, but i cant get it done alone, maybe its all the " and & and ' and _ in vba code that i am doing wrong, so please be specific. thanks i appreciate it a lot

 

[SydneyGeek]

Change this...

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\import\ &filename" _
, Destination:=Range("$A$12"))
.Name = "filename"

to this...

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\import\" & filename &  _
", Destination:=Range("$A$12"))
.Name = filename

When you include a variable into some text you need to join everything to the left (surrounded by quotes) to your variable and then anything to the right (again, in quotes. If you don't do that, VBA will take the variable as a literal piece of text, and fail when (in this case) the file called "C:\import\filename" is not found.
Denis

 

[unitedmre]

hey sydneygeek,

thanks for the proposed changes. however, i did as you said, and now i get the following message when i run this macro:

also, can you recommend an online beginners guide or a book for understanding VBA on a basic level? its so **** helpful and i would like to understand it a little bit more, at least to the degree that i can implement code proposed by others, or make minor changes to recorded macros successfully.
thanks!

 

[SydneyGeek]

You have to watch the spacing. Note in my example, the & character had a space before filename. Yours didn't... and it makes a difference.

The best free resource for VBA that I know of is this Board. Do some searches, dig through the examples, and you will learn a lot. But if you want a book in your hand take a look at the books in the mrExcel book store. At least one of them covers VBA; I would recommend Bill's Excel 2007 book.
Another author to look at is John Walkenbach. He has written Excel xx Power Progamming With VBA (where xx is the version) and Excel VBA for Dummies.

Walk into a bookshop, pick up a few books and leaf through them. Get the one whose style you like.

Denis

 

[unitedmre]

ok thanks. for now, i still need help with that macro.
i removed the spacing error, still get error message, even though code is now exactly as you recommend.
when i click through the code,i also get this error here, maybe this causes the problem:

 

[SydneyGeek]

Just type A12 for the range, and it should work. The fixed reference symbols are not valid when you use Range.

Denis

 

[unitedmre]

thanks for willing to help - i feel obliged to pay for this

now i get this:

Just type A12 for the range, and it should work. The fixed reference symbols are not valid when you use Range.

Denis

 

[Sektor]

I guess this:

With Sheet1.QueryTables.Add(Connection:= _
    "TEXT;C:\import\" & filename, Destination:=Range("A12").....
......

 

[Peter_SSs]

Welcome to the MrExcel board!

i feel obliged to pay for this

Hopefully that was a light-hearted remark, as we do not allow that here. See Solicitation. All help that is given here is given because people want to help.

 

[unitedmre]

great! thanks a lot Sektor and SydneyGeek! its all working now, i made a nice button and can specify the file i need easily with another concatenate function saves a lot of time browsing/searching.


@Peter_SSs: lighthearted but really, its weird to have access to a pool of knowledge that would cost serious $$$ otherwise, with nothing in return than a simple thx.

I guess this:

With Sheet1.QueryTables.Add(Connection:= _
    "TEXT;C:\import\" & filename, Destination:=Range("A12").....
......