import excel file into existing file

VBAnewbie · Aug 27th 2004

Hi,
I have the following problem. I hope someone is able to offer me help.

When I want to import an existing file from a excel file stored in my computer, i use the following lines of codes:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Z:\Kim\Sept 2003\2003\home\ftp\pub\account\lmp\20030930.csv" _
, Destination:=Range("A1"))

The problem is, I have to import many files for each day, namely, 20030929
,20030928 ... How can I add a parameter variable into codes like this?
So, I want something like this (but obviously this doesnt work)

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Z:\Kim\Sept 2003\2003\home\ftp\pub\account\lmp\2003[date].csv" _
, Destination:=Range("A1"))

So, I just need to input "date" (0929, 0927...etc)

Thanks,
Kim

shades · Aug 27th 2004

I use a generic code that allows me to search through directory to open appropriate files.

Code

myFile = Application.GetOpenFilename
    Workbooks.Open Filename:=myFile

Not sure if this is what you are looking for.

bnix · Aug 27th 2004

You could use something similar to the below. It would prompt you for a date and then run the code based on the answer

Code

Dim ReportDate As String
ReportDate = Application.InputBox("Enter Date Of Report", "Date", Type:=2)
'formats input to 0825 day & month
ReportDate = Format(ReportDate, "mmdd")


With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Z:\Kim\Sept 2003\2003\home\ftp\pub\account\lmp\2003" & ReportDate & ".csv" _
, Destination:=Range("A1"))
End With

VBAnewbie · Aug 27th 2004

Hi, Thank you. Your codes are what I'm looking for. Thanks.

Kim

import excel file into existing file

Participate now!

Share