Results 1 to 4 of 4
  1. #1
    Join Date
    May 2006
    Location
    NYC
    Posts
    30

    Unanswered: VBA code that will import a forever changing file.

    Greetings again. I have started to read thru a number of the posts to learn more about Access and it's been helpful except for the heavy duty VBA discussions. As for myself, I've been geting away with easy do.cmd codes but now I like to go one step ahead.

    I created a macro and turned it into VBA Code. It works and even I was impressed. It reads like this.

    Function mcrImport()
    On Error GoTo mcrImport_Err
    DoCmd.TransferText acImportDelim, "Citibank Import specification", "tblUploadFromCitiTest", "c:\checking.csv", False, ""
    mcrImport_Exit:
    Exit Function
    mcrImport_Err:
    MsgBox Error$
    Resume mcrImport_Exit
    End Function


    So, here my problem. My import file changes daily. i.e. the file will always start as follows checking_ and followed by today's date. So it reads like this checking_05312006.csv. Tomorrow it will read checking_06012006.csv

    Instead of always having to always rename my file and save it with the same name all the time I like to add vba code to take in the file automatically taking into consideration of the new name.

    I read a earlier post and figured I could use an "&" a Date function but that didn't work.

    Well, I appreciate any ideas or direction from anyone.

    Thank you,
    Wendy

    P.S. I just love this beer tapping smiley faces. Cyberspace beer for everyone! On me!

  2. #2
    Join Date
    Oct 2003
    Posts
    21
    Try something like......

    Function mcrImport()
    On Error GoTo mcrImport_Err

    Dim dFileDate As Date
    Dim sFileDate As String
    Dim sFileName As String

    'Get the file date from the user
    dFileDate = CDate(InputBox("Please Enter File Date mm/dd/yy", "Enter Date"))

    'Change the user input to mmddyyyy format
    sFileDate = Format(dFileDate, "mmddyyyy")

    'Build the file name
    sFileName = "checking_" & sFileDate & ".csv"

    'Import the file
    DoCmd.TransferText acImportDelim, "Citibank Import specification", "tblUploadFromCitiTest", sFileName, False, ""

    mcrImport_Exit:
    Exit Function
    mcrImport_Err:
    MsgBox Error$
    Resume mcrImport_Exit
    End Function


    Note there is no user input data checking involved there, that is something you would need to add.

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if you are confident that the file will always be there, and the user's clock will always be on the correct day, you don't need the user input

    sFileDate = Format(dFileDate, "mmddyyyy")
    becomes:
    sFileDate = Format(Now(), "mmddyyyy")

    and you don't need the inputbox() line

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    May 2006
    Location
    NYC
    Posts
    30
    Thank you Gussery and Cavalier! Both codes together works like a charm! I have made a note in the procedure giving you both credit.

    'Kudos to Gussery and Cavalier from DBForums for making this code possible!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •