Annmv888
asked on
Transfer excel data to access table by choosing file from windows explorer.
I’m trying to find vba to transfer an Excel file of my choosing from Windows Explorer into an existing table in Access without opening the Excel file.
I would like this to be attached to a button's on click event in Access.
I've only found code to open a folder but the file is opened when clicked and also one that only shows the folders and not the files within the folder. Any help would be appreciated.
I would like this to be attached to a button's on click event in Access.
I've only found code to open a folder but the file is opened when clicked and also one that only shows the folders and not the files within the folder. Any help would be appreciated.
ASKER
I do but it transfers a range from the current days Excel document. They have to be able to make their own choice.
Here it is:
Dim strXls As String
strXls = CurrentProject.Path & Chr(92) & "Sent to AP\ChecksReceived" & Format(Date, "mmddyyyy") & ".xls"
DoCmd.TransferSpreadsheet acImport, , "tblChecksReceived", _
strXls, True, "APChecks"
Here it is:
Dim strXls As String
strXls = CurrentProject.Path & Chr(92) & "Sent to AP\ChecksReceived" & Format(Date, "mmddyyyy") & ".xls"
DoCmd.TransferSpreadsheet acImport, , "tblChecksReceived", _
strXls, True, "APChecks"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you want them to specify a range, you will need to link the worksheet rather than importing it.
DoCmd.TransferSpreadsheet acLink, , "tblChecksLinked", _
strXls, True, "APChecks"
On your form, create two unbound textboxes. One with a start date and the other with an end date. If most of the time you only want to import a single day, have the afterUpdate event of the first textbox put the date into the end text box
Me.txtEndDT = Me.txtStartDT
Now create a query that selects rows for this date range and appends them to tblChecksReceived.
To set this up the first time, you will need to link the spreadsheet manually so that the table named tblChecksLinked exists which will allow you to create the append query. After the first time, the code will handle the linking.
DoCmd.TransferSpreadsheet acLink, , "tblChecksLinked", _
strXls, True, "APChecks"
On your form, create two unbound textboxes. One with a start date and the other with an end date. If most of the time you only want to import a single day, have the afterUpdate event of the first textbox put the date into the end text box
Me.txtEndDT = Me.txtStartDT
Now create a query that selects rows for this date range and appends them to tblChecksReceived.
To set this up the first time, you will need to link the spreadsheet manually so that the table named tblChecksLinked exists which will allow you to create the append query. After the first time, the code will handle the linking.
ASKER
This worked exactly how I wanted it to...thank you so much for the help.
If you have that it would be easy to add functionality to it that allows you to select the file to import.