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

    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.

    Thanks

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    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
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    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.

  4. #4
    Join Date
    Oct 2009
    Posts
    8
    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 vb.net 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
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Easy enough to achieve in Access!

    http://www.dbforums.com/microsoft-ac...ml#post6280858

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

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

  7. #7
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    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:

    Code:
    Private Sub cmdBrowse_Click()
    
        Dim dlgBrowse As FileDialog
        
        Set dlgBrowse = Application.FileDialog(msoFileDialogFolderPicker)
        
        With dlgBrowse
            
            .AllowMultiSelect = False
            If txtPath <> "" Then .InitialFileName = txtPath
            .Show
            
            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
    Posts
    8
    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
  •