Results 1 to 4 of 4
  1. #1
    agent- is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    45

    Import Excel with Variable filename, static location

    Hello,



    I am trying to be able to import an excel name that will have a static location, and a static 1st half of the name. The second half will change. I am using a VBS script to do multiple processes, so I can move the file after it has been uploaded so that there is only one file with that file name in the folder.

    DoCmd.TransferSpreadsheet acImport, , "Commission", "Location/static" + * + ".xls", true

    Is there anyway to do this? If that can not be done, is there anyway to have the dynamic part be MM_YYYY which can be from 1-3 months behind NOW. That may be the easier way if I can have it scan for a file with one of those names instead.

    Any help would be greatly appreciated.

  2. #2
    agent- is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    45
    Here is my current code. I am getting a "date type error" when I try to add the month/date. it worked if I put in the full file name.

    Dim FileName

    If FileExists("C:\Documents and Settings\Rkolofer\My Documents\Commission\Yum_Brands_" + Month(Date - 30) + "_" + Year(Date - 30) + "xlsx") Then
    FileName = "Yum_Brands_" + Month(Date - 30) + "_" + Year(Date - 30) + ".xlsx"
    DoCmd.TransferSpreadsheet acImport, , "Commission", "C:\Documents and Settings\rkolofer\My Documents\Commission\" + FileName, True


    ElseIf FileExists("C:\Documents and Settings\Rkolofer\My Documents\Commission\Yum_Brands_" + Month(Date - 60) + "_" & Year(Date - 60) + "xlsx") Then
    FileName = "Yum_Brands_" + Month(Date - 60) + "_" & Year(Date - 60) + ".xlsx"
    DoCmd.TransferSpreadsheet acImport, , "Commission", "C:\Documents and Settings\rkolofer\My Documents\Commission\" + FileName, True

    ElseIf FileExists("C:\Documents and Settings\Rkolofer\My Documents\Commission\Yum_Brands_" + Month(Date - 90) + "_" + Year(Date - 90) + "xlsx") Then
    FileName = "Yum_Brands_" + Month(Date - 90) + "_" + Year(Date - 90) + ".xlsx"
    DoCmd.TransferSpreadsheet acImport, , "Commission", "C:\Documents and Settings\rkolofer\My Documents\Commission\" + FileName, True

    Else
    DoCmd.Close
    End If
    ---------------------------------------------------------------------
    Function FileExists(ByVal strFile As String, Optional bFindFolders As Boolean) As Boolean
    'Purpose: Return True if the file exists, even if it is hidden.
    'Arguments: strFile: File name to look for. Current directory searched if no path included.
    ' bFindFolders. If strFile is a folder, FileExists() returns False unless this argument is True.
    'Note: Does not look inside subdirectories for the file.
    'Author: Allen Browne. http://allenbrowne.com June, 2006.
    Dim lngAttributes As Long

    'Include read-only files, hidden files, system files.
    lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)

    If bFindFolders Then
    lngAttributes = (lngAttributes Or vbDirectory) 'Include folders as well.
    Else
    'Strip any trailing slash, so Dir does not look inside the folder.
    Do While Right$(strFile, 1) = "\"
    strFile = Left$(strFile, Len(strFile) - 1)
    Loop
    End If

    'If Dir() returns something, the file exists.
    On Error Resume Next
    FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
    End Function

    Any idea how to change my code? Almost there!

  3. #3
    khalid's Avatar
    khalid is offline MS-Access Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2010
    Location
    Kuwait
    Posts
    244
    Code:
    Dim FileName
    
    If FileExists("C:\Documents and Settings\Rkolofer\My Documents\Commission\Yum_Brands_" + Month(Date - 30) + "_" + Year(Date - 30) + ".xlsx") Then
    FileName = "Yum_Brands_" + Month(Date - 30) + "_" + Year(Date - 30) + ".xlsx"
    DoCmd.TransferSpreadsheet acImport, , "Commission", "C:\Documents and Settings\rkolofer\My Documents\Commission\" + FileName, True
    
    
    ElseIf FileExists("C:\Documents and Settings\Rkolofer\My Documents\Commission\Yum_Brands_" + Month(Date - 60) + "_" & Year(Date - 60) + ".xlsx") Then
    FileName = "Yum_Brands_" + Month(Date - 60) + "_" & Year(Date - 60) + ".xlsx"
    DoCmd.TransferSpreadsheet acImport, , "Commission", "C:\Documents and Settings\rkolofer\My Documents\Commission\" + FileName, True
    
    ElseIf FileExists("C:\Documents and Settings\Rkolofer\My Documents\Commission\Yum_Brands_" + Month(Date - 90) + "_" + Year(Date - 90) + ".xlsx") Then
    FileName = "Yum_Brands_" + Month(Date - 90) + "_" + Year(Date - 90) + ".xlsx"
    DoCmd.TransferSpreadsheet acImport, , "Commission", "C:\Documents and Settings\rkolofer\My Documents\Commission\" + FileName, True
    
    Else
    DoCmd.Close
    End If
    Change as highlighted and see what happens?

  4. #4
    agent- is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    45
    Fixed it:

    If (Month(Date - 30) < 10) Then
    Month1 = "0" & Month(Date - 30) & "_" & Year(Date - 30)
    Else
    Month1 = Month(Date - 30) & "_" & Year(Date - 30)
    End If

    If (Month(Date - 60) < 10) Then
    Month2 = "0" & Month(Date - 60) & "_" & Year(Date - 60)
    Else
    Month2 = Month(Date - 60) & "_" & Year(Date - 60)
    End If

    If (Month(Date - 90) < 10) Then
    Month3 = "0" & Month(Date - 90) & "_" & Year(Date - 90)
    Else
    Month3 = Month(Date - 90) & "_" & Year(Date - 90)
    End If


    If FileExists("C:\Documents and Settings\Rkolofer\My Documents\Commission\Yum_Brands_" & Month1 & ".xlsx") Then
    FileName = "Yum_Brands_" & Month1 & ".xlsx"
    DoCmd.TransferSpreadsheet acImport, , "Commission", "C:\Documents and Settings\rkolofer\My Documents\Commission\" & FileName, True

    ElseIf FileExists("C:\Documents and Settings\Rkolofer\My Documents\Commission\Yum_Brands_" & Month2 & ".xlsx") Then
    FileName = "Yum_Brands_" & Month2 & ".xlsx"
    DoCmd.TransferSpreadsheet acImport, , "Commission", "C:\Documents and Settings\rkolofer\My Documents\Commission\" & FileName, True

    ElseIf FileExists("C:\Documents and Settings\Rkolofer\My Documents\Commission\Yum_Brands_" & Month3 & ".xlsx") Then
    FileName = "Yum_Brands_" & Month3 & ".xlsx"
    DoCmd.TransferSpreadsheet acImport, , "Commission", "C:\Documents and Settings\rkolofer\My Documents\Commission\" & FileName, True

    Else
    MsgBox ("Yum_Brands_" & Month1 & ".xlsx")
    End If
    -------------------------------------------------------------
    Is there any easier way to do the If Then statements for the Month? My code is just adding a 0 if there isnt two values in the month since thats how the format is, but this seems like it can be optimized.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 03-01-2011, 10:47 AM
  2. Excel Import
    By jesleon in forum Import/Export Data
    Replies: 4
    Last Post: 08-24-2010, 07:32 AM
  3. Import Excel with SQL - again
    By tpcervelo in forum Queries
    Replies: 1
    Last Post: 08-06-2010, 08:13 PM
  4. VBA to open excel, import on close of excel
    By bdaniel in forum Programming
    Replies: 0
    Last Post: 03-20-2010, 02:45 PM
  5. Prompt for filename for import/export??
    By stephenaa5 in forum Import/Export Data
    Replies: 23
    Last Post: 10-23-2009, 03:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums