Results 1 to 2 of 2
  1. #1
    AKQTS is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jul 2010
    Posts
    56

    Browse for Excel File and Import

    I have been playing with this for a while and do not know my next step.



    The goal is to have the operator be able to click a button and browse for an excel file. Which it can do.

    Next is when they select the excel file it imports it into a table which I am still trying to accomplish.

    Any ideas? Thanks

    Code:
    Private Sub Pack_Slip_Click()
     
    If MsgBox("This will open the Excel folder for spreadsheet imports. Continue?", vbYesNoCancel) = vbYes Then
     
    Dim i As Integer
    Dim tblStr As String
    Dim varItem As Variant
     
    i = 1
    tblStr = ""
    With Application.FileDialog(msoFileDialogFilePicker)
    With .Filters
    .Clear
    .Add "All Files", "*.*"
    End With
    .AllowMultiSelect = True
    .InitialFileName = "C:"
    .InitialView = msoFileDialogViewDetails
    If .Show Then
    For Each varItem In .SelectedItems
    For i = 1 To Len(varItem)
    If IsNumeric(Mid(CStr(varItem), i, 1)) Then
    tblStr = tblStr & Mid(CStr(varItem), i, 1)
    End If
    Next i
    If Right(CStr(varItem), 4) = ".xlsx" Then
    If MsgBox("Do you want to import <insert name here>?", vbYesNoCancel, "Verify") = vbYes Then
    DoCmd.TransferSpreadsheet acImport, , tblStr, CStr(varItem), True
    i = i + 1
    DoCmd.OpenTable tblStr, acViewNormal, acReadOnly
    MsgBox "Data Transferred Successfully!"
    DoCmd.Close
    tblStr = ""
    End If
    End If
    Next varItem
    DoCmd.Close
    End If
    End With
    End If
     
    Pack_Slip_Exit:
    Exit Sub
    Pack_Slip_Err:
    MsgBox Error$
    Resume Pack_Slip_Exit
    End Sub

  2. #2
    AKQTS is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jul 2010
    Posts
    56
    This code below works great to import, but the true goal is to append the data to another table. The xls files are all the same structurem they just contain different amounts of records.

    Code:
    Private Sub Command0_Click()
    If MsgBox("This will open the Excel folder for spreadsheet imports. Continue?", vbYesNoCancel) = vbYes Then
    Dim i As Integer
    Dim tblStr As String
    Dim varItem As Variant
    i = 1
    tblStr = ""
    With Application.FileDialog(msoFileDialogFilePicker)
    With .Filters
    .Clear
    .Add "All Files", "*.*"
    End With
    .AllowMultiSelect = True
    .InitialFileName = "c:\exlbooks"
    .InitialView = msoFileDialogViewDetails
    If .Show Then
    For Each varItem In .SelectedItems
    For i = 1 To Len(varItem)
    If IsNumeric(Mid(CStr(varItem), i, 1)) Then
    tblStr = tblStr & Mid(CStr(varItem), i, 1)
    End If
    Next i
    If Right(CStr(varItem), 4) = ".xls" Then
    DoCmd.TransferSpreadsheet acImport, , tblStr, CStr(varItem), True
    i = i + 1
    DoCmd.OpenTable tblStr, acViewNormal, acReadOnly
    MsgBox "Data Transferred Successfully!"
    DoCmd.Close
    tblStr = ""
    End If
    Next varItem
    DoCmd.Close
    End If
    End With
    End If
    End Sub

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

Similar Threads

  1. Importing Excel file. (2 non numeric values won't import)
    By Amerigo in forum Import/Export Data
    Replies: 3
    Last Post: 05-20-2011, 11:31 AM
  2. Excel Import - File with row spaces
    By Jackfam58 in forum Import/Export Data
    Replies: 1
    Last Post: 04-13-2011, 07:35 PM
  3. Import Excel File Using Macro?
    By oregoncrete in forum Import/Export Data
    Replies: 0
    Last Post: 04-05-2011, 12:26 PM
  4. Import Excel file as report template - possible?
    By justinwright in forum Reports
    Replies: 2
    Last Post: 11-01-2010, 07:01 AM
  5. Browse for file
    By ccpine@comcast.net in forum Database Design
    Replies: 0
    Last Post: 08-24-2008, 10:12 AM

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