import excel file into existing file

  • 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)


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

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

    Not sure if this is what you are looking for.

    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt

    Old, slow, and confused - but at least I'm inconsistent!

    (retired Excel 2003 user, 3.28.2008)

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

    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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!