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