Table to Excel in One click of a button

[cos]

export table to excel in runtime

hi guys,

is there any way i can create a button on a form, to export a whole table to excel and save it?

cuz this database will be used in runtime version, and there's no option to export data like there is in normal access..

i know that i could link an excel file to access and just make auto update, so that it constantly updates its values with the table, and then just go on this excel file and click save as; but could i create a new excel file from a wanted table, and then save it?

i hope some of this makes sense..

 

[vbaInet]

Re: export table to excel in runtime

See here for exporting to Excel:

http://www.btabdevelopment.com/ts/default.aspx?PageId=10

 

[Pat Hartman]

Re: export table to excel in runtime

Nice reference site VBA but this code is serious overkill for the specific request. A one line piece of code using TransferSpreadsheet will almost certianly suffice.

A couple more lines will be needed if the parts of the statment need to be customized at runtime.

 

[vbaInet]

Re: export table to excel in runtime

That's Bob's website by the way. I think he mentions the TransferSpreadsheet method in one of the links.

 

[boblarson]

Re: export table to excel in runtime

Nice reference site VBA but this code is serious overkill for the specific request. A one line piece of code using TransferSpreadsheet will almost certianly suffice.

Unless you want to do specific formatting without using a template.

 

[cos]

Re: export table to excel in runtime

1. i placed the "Export A Table Or Query To Excel" code in a new module, named it ShopsToExcel.
   
2. changed the: strTQName - TBL_Shop
   and strSheetName - TBL_Shop2
   
3. created a button on my little form.
   
4. made an OnClick event for my button, chosen macro, and set "Run code" and pasted: SendTQ2Excel
   
5. then i recieve this little feller (as attached)..
   
   any ideas on what im doing wrong?

 

[boblarson]

Re: export table to excel in runtime

First off

1. #1 is correct

2. #2 is INCORRECT. You don't change the strTQName. LEAVE IT AS IS.

3. #3 Is correct

4. #4 is 1/2 correct. The code should use
SendTQ2Excel "TBL_Shop"

(you are passing the table name to the function. You don't change the function to use the table name because then it isn't generic and reusable. That's what function parameters are for).

 

[cos]

Re: export table to excel in runtime

Thx Bob!! its working now!

many thanks

 

[CanWest]

Re: export table to excel in runtime

First off

1. #1 is correct

2. #2 is INCORRECT. You don't change the strTQName. LEAVE IT AS IS.

3. #3 Is correct

4. #4 is 1/2 correct. The code should use
SendTQ2Excel "TBL_Shop"

(you are passing the table name to the function. You don't change the function to use the table name because then it isn't generic and reusable. That's what function parameters are for).

I have done a similar thing. My code reads

Call SendTQ2Excel("qry_ExportCompanyList", "Company List")

I get an error Too few parameters. Expected 4

Any suggestions