Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2009

    Unanswered: Import XLS file using browse

    I have a table in access that I need to import an excel spreadsheet. I would like to be able to automate this as much as possible. Problem is that the file name changes everyday. My thoughts are that I could set a command button to open a browse menu and select the file then have access import the file.

    Does anyone have ant other ideas?

    If anyone has any sample code that would do this please help.


  2. #2
    Join Date
    Nov 2007
    Adelaide, South Australia
    Can't answer the first question since you haven't provided any details on HOW the name changes every day. Is there a pattern to it?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 18
    Using the file system object allows you to inspect the extension and modified date of a file. Assuming that the Excel file is always in the same directory, you could code an import process to pick up the youngest Excel file that it finds there.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  4. #4
    Join Date
    Oct 2009
    Quote Originally Posted by StarTrekker
    Can't answer the first question since you haven't provided any details on HOW the name changes every day. Is there a pattern to it?
    The file is saved everyday by a user. the name would be olditems101209.xls then olditems101309.xls so on and so on. I don't want to automate the procedure but more force the person responsible of updating the information to step through the process.

    Step 1 clear the table on Monday
    Step 2 click update button browse to directory select today's file
    Step 3 have the table updated.

    Steps 2 and 3 would be done everyday and on Friday a graph would be printed out charting all missing items.

    Process starts over on Monday.

    Do this help?

    I believe I could do this a lot easier using front end with an access database back end but I am forced to everything in access. Any help in appreciated.

  5. #5
    Join Date
    Jan 2007
    Provided Answers: 12
    Easy enough to achieve in Access!

    I knew I'd uploaded something like this before
    Home | Blog

  6. #6
    Join Date
    Oct 2009
    Thanks I will work with this today.

  7. #7
    Join Date
    Feb 2004
    Chicago, IL
    If you have Microsoft office loaded (probably do) then you can add a reference to it and use the Common Dialogs for browsing. I believe when you are adding a reference, press the Browse button, and link to the MSO.DLL in the folder:

    C:\Program Files\Common Files\Microsoft Shared\OFFICE12

    I am using Office XP. The folder will vary depending on the version you are using. THen I have the following code in the Click event of a button on my form:

    Private Sub cmdBrowse_Click()
        Dim dlgBrowse As FileDialog
        Set dlgBrowse = Application.FileDialog(msoFileDialogFolderPicker)
        With dlgBrowse
            .AllowMultiSelect = False
            If txtPath <> "" Then .InitialFileName = txtPath
            If .SelectedItems.Count <> 0 Then
                txtPath = .SelectedItems(1) & IIf(Right(.SelectedItems(1), 1) = "\", "", "\")
            End If
        End With
        Set dlgBrowse = Nothing
    End Sub
    I use this code to pick a folder but you can change it to select a file.

  8. #8
    Join Date
    Oct 2009
    Thanks for all the help. Using some of the provided code I been able to import the data as needed.

Posting Permissions

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