Hi,
I'm learning VBA/Access coding as I go at work, hit a wall in my current project and looking for some help.
I have a workbook that is updated biweekly, it has a number of worksheets but I only need to import two of them into my Access database into two separate tables. One will always be the first sheet in the workbook, the other may not have a set position but will have a set name.
I use a file picker module I found the code for online to select the Excel file needed, as it is unknown who will be maintaining it later on or where they will store the files. I want to make this as "one-click" as possible.
So there's two avenues I can go down, I'm not sure which is easier/possible
1) Import both sheets at once and populate the appropriate tables
2) Somehow save the full file path from the File Picker, that I can store as a string and then call in a second DoCmd.TransferSpreadsheet line. This is what I have been trying and failing at.
Here's my applicable code
File Picker Module, I added the myfile part to try and get the file path:
Button to import the Excel File
As it is now, I get an error "The action or method requires a File Name Argument" which I thought I had done. I have tried instead of "myfile" fd.selectFile and fd.SelectedItems(1) but those did not work either. I do not want another File Select popup as that might be confusing to the end user.
I feel like I'm missing something really simple and obvious
I'm learning VBA/Access coding as I go at work, hit a wall in my current project and looking for some help.
I have a workbook that is updated biweekly, it has a number of worksheets but I only need to import two of them into my Access database into two separate tables. One will always be the first sheet in the workbook, the other may not have a set position but will have a set name.
I use a file picker module I found the code for online to select the Excel file needed, as it is unknown who will be maintaining it later on or where they will store the files. I want to make this as "one-click" as possible.
So there's two avenues I can go down, I'm not sure which is easier/possible
1) Import both sheets at once and populate the appropriate tables
2) Somehow save the full file path from the File Picker, that I can store as a string and then call in a second DoCmd.TransferSpreadsheet line. This is what I have been trying and failing at.
Here's my applicable code
File Picker Module, I added the myfile part to try and get the file path:
Code:
Option Compare Database
'--------------------------------------------------
' File Browse Code
'--------------------------------------------------
'NOTE: To use this code, you must reference
'The Microsoft Office 14.0 (or current version)
'Object Library by clicking menu Tools>References
'Check the box for:
'Microsoft Office 14.0 Object Library in Access 2010
'Microsoft Office 15.0 Object Library in Access 2013
'Click OK
'--------------------------------------------------
Function selectFile()
Dim fd As FileDialog
Dim myfile As String
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
If .Show Then
selectFile = .SelectedItems(1)
myfile = .SelectedItems(1)
Else
'stop execution if nothing selected
End
End If
End With
Set fd = Nothing
End FunctionButton to import the Excel File
Code:
Public Sub testbutton_Click()
On Error GoTo testbutton_Click_Err
'load updated data spreadsheet (always 1st tab)
DoCmd.TransferSpreadsheet acImport, 8, "NewData", selectFile(), True
'load change log sheet
DoCmd.TransferSpreadsheet acImport, 8, "UpdateInfo", myfile, True, "Update Info!"
testbutton_Click_Exit:
Exit Sub
testbutton_Click_Err:
MsgBox Error$
Resume testbutton_Click_Exit
End SubAs it is now, I get an error "The action or method requires a File Name Argument" which I thought I had done. I have tried instead of "myfile" fd.selectFile and fd.SelectedItems(1) but those did not work either. I do not want another File Select popup as that might be confusing to the end user.
I feel like I'm missing something really simple and obvious
