exporting data from access to excel

[tariq nawaz]

being a novice user not familiar with databases much but trying to export my access table in excel.



i will be highly appreciated if someone can help me in that.i am very new to databases and vba and i hope you wont mind to help my stupid questions.


i have the following code which gives an error when i try to implement it.

Private Sub Command22_Click()
Dim strExcelFile As String
Dim strWorksheet As String
Dim strDB As String
Dim strTable As String
Dim objDB As Database


'Change Based on your needs, or use
'as parameters to the sub
strExcelFile = "C:\My Documents\M.xls" (this is the name of the excel file)
strWorksheet = "sheet1" (this is the name of the sheet in work book)
strDB = "C:\Users\TARIQ\Desktop\w\data_input.accdb" (this is the location where my database file lying)
strTable = "main" ("main" is the name of the table in my data_intput.accdb database)


Set objDB = OpenDatabase(strDB)


'If excel file already exists, you can delete it here
If Dir(strExcelFile) <> "" Then Kill strExcelFile


objDB.Execute _
"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet & "] FROM " & "[" & strTable & "]"
objDB.Close
Set objDB = Nothing
End Sub

as i am no new to the databases so please help me if i am missing something.

regards

A learner

[JoeM]

Why not just use the TransferSpreadsheet command?
If you create it in a Macro, it gives a detailed explanation of each argument.
You can then convert that Macro to VBA code by using the "Convert Macros to Visual Basic" utility found in Access on the Macro menu/ribbon.
You can then modify/insert that VBA code into your other VBA code.

[tariq nawaz]

Why not just use the TransferSpreadsheet command?
If you create it in a Macro, it gives a detailed explanation of each argument.
You can then convert that Macro to VBA code by using the "Convert Macros to Visual Basic" utility found in Access on the Macro menu/ribbon.
You can then modify/insert that VBA code into your other VBA code.

highly apreciated.
can you please give me detailed information to do that.
it will be highly apreciated.
as i have copied the code so i literally tried to understand the code.
being a novice user but want to learn by practice.

help will be really apreciated.

Regards

A learner

[JoeM]

can you please give me detailed information to do that.

That's the great thing about using a Macro to do it. You don't need to know the first thing about coding to do it. Access makes it really easy.
Just go in to the Create menu and select "Macro" from the Other ribbon.
On the Show/Hide ribbon, make sure that the "Show All Actions" button is selected.
Then down in the grid, select the "TransferSpreadsheet" Action.
Then fill out all the appropriate Action Arguments. Note if you click on each one, there is information off to the right which helps you along in filling these out. And if you click F1, it will give you more detailed help.

When finished, you can click on it to run it and verify it works.
If you would like to then convert it to Visual Basic code, highlight the Macro name, go to the Database Tools menu, and on the Macro ribbon, select "Convert Macros to Visual Basic".