Hey All!
I have a problem. I am creating a screen that includes persons first_name, last_name, phone_number (this is a fields in access)
Now I have created a command button and I want that when user clicks on the command button the information in those fields would go to excel page.
Sincerely,
Boris
There's a function in Access that lets you export information to Excel. It's called TransferSpreadsheet.
DoCmd.TransferSpreadsheet acImport, 3, _
"Employees","C:\Lotus\Newemps.wk3", True, "A1:G12"
acImport can be set to acExport
3 indicates what version of Excel you want to target
True is for has field names which includes a top row containing the names of the fields in the table or query.
You can supply a SELECT statement or table name.
Put it behind the command button you want the user to press.
Your screen, if it's bound, will have a table or a query behind it, by that I mean the information on the screen will be coming from a query or a table. You'll be displaying the person's last name, first name, etc information in textboxes on this screen.
So what you want to do is take the information in the text boxes, filter the query, and export the filtered information to Excel. Lets say the screen is bound to a query named [qryEmployees] and the text boxes are called txtFirst and txtLast.
Dim sSQL as String
sSQL = "SELECT * FROM [qryEmployees] WHERE [Firstname] = '" & txtFirst.Value & "' AND [Lastname] = '" & txtLast.Value & "';"
DoCmd.TransferSpreadsheet acExport, 3, sSQL,"C:\Exported.xls", True, "A1:G12"
Good answer! Maybe you can help me for a further more complex question:
How can I parametrize Docmd.TransferSpreadsheet ?
I created a form to let the user choose disk unit, folder and filename, but I don't know how to insert correctly the user choice in the TransferSpreadsheet syntax.
Can you or someone else help me ?
It seems I'm the only one in the world to experiment this problem... ;-(
Hello Roberto,
You need to pass the path to the method like this...
Dim sFile as String
sFile = "C:\Folder\Filename.xls"
DoCmd.TransferSpreadsheet acExport, 3, sSQL, sFile, True, "A1:G12"
Whatever the user enters store it as a string and pass it into the transfer spreadsheet method as shown.
