Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2012
    Posts
    3

    Question Unanswered: Import excel 2003 file to access 2003 database

    hi, I'm new here and new to vba...

    i want to be able to import an excel file into access 2003 into an existing access table and want to prompt the user to select the excel file. I have been able to import the excel file automatically into access using the macro design toolbar, but it only imports a specific file

    this is the code i have...any help would be appreciated

    thanks


    Function Import_Excel_Void_Shipment_List()
    On Error GoTo Import_Excel_Void_Shipment_List_Err

    DoCmd.OpenTable "Test Data", acViewNormal, acEdit
    DoCmd.GoToRecord acTable, "Test Data", acNewRec
    DoCmd.TransferSpreadsheet acImport, 8, "Test Data", "Void Shipments - test data", True, ""
    DoCmd.OpenTable "Test Data", acViewNormal, acEdit
    DoCmd.GoToRecord acTable, "Test Data", acNewRec


    Import_Excel_Void_Shipment_List_Exit:
    Exit Function

    Import_Excel_Void_Shipment_List_Err:
    MsgBox Error$
    Resume Import_Excel_Void_Shipment_List_Exit

    End Function

  2. #2
    Join Date
    Aug 2012
    Posts
    31
    I'm curious to see how this turns out for you. I was looking at doing this last week w/ the DoCmd.TransferSpreadsheet method, but ended up just creating a subform with the table and having the user paste into that. Then moved that data into another table and clearing the original.

    Good luck. If no one here knows how to do it I'll see if I can work through it with you. I'm sure lots of people would find the code useful. Will the excel file always have the same name and be in the same place, or are you going to have them browse to it?

  3. #3
    Join Date
    Aug 2012
    Posts
    31
    This worked for me when I was playing around just now. It only imported the fields that have a value. The column header had to match the field name in the table or I got an error message, the order didn't seems to make a differene becuase I had left the first field in the table as the autonumber primary key, and it still put the data in the right place. Then again I only imported one column. I'm sure you could replace c:\test.xls with a string varible that your user assigns by browsing.

    Another nice addition would be to verify that you have the correct tab.

    Code:
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        "Table1", "C:\test.xls", True, "A1:A65"

  4. #4
    Join Date
    Aug 2012
    Posts
    3
    i want to have the user browse to the file, but that is what i'm unsure of how to do...I also like you suggestion about validating the correct tab...

  5. #5
    Join Date
    Aug 2012
    Posts
    31
    Did you get any further with this?

  6. #6
    Join Date
    Aug 2012
    Posts
    3
    yes, I was able to find some code that seemed to work with a little modification...

    Public Sub Procedure()

    Dim myDialog As FileDialog
    Dim strFile As String
    Dim strSearchPath As String
    Dim vrtSelectedItem As Variant
    Set myDialog = Application.FileDialog(msoFileDialogOpen)

    With myDialog
    .AllowMultiSelect = True
    .Filters.Add "Excel Files", "*.xls", 1
    .Title = "Please Locate the Files to Import!"
    .InitialFileName = strSearchPath
    If .Show = -1 Then

    For Each vrtSelectedItem In .SelectedItems

    Import_SelectedItem (vrtSelectedItem)
    Next vrtSelectedItem
    MsgBox "Data will now be imported into Access Table"
    Else

    End If

    Set myDialog = Nothing
    End With

    End Sub

Posting Permissions

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