Question about exporting to Excel

[greaseman]

I've got a project that takes MS Access data and puts it into an Excel worksheet. I am interested in havig a dialog window open up that will allow me to specify the folder and Excel file name to save my data into. If anyone has a way of doing ths, I wold love to hear from them.

Incidentally, I do not want to use the TransferSpreadsheet function, since I am reporting data that would normally go into a report, and I am instead exporting my data into an Excel worksheet that looks like my report, with bolding, underlining, subtotals and totals.

Thanks in advance!

 

[Advertiser]

Someone posted something here and it was not me. I got an email saying a login attempt had failed. I do not understand.

 

[greaseman]

???? I did not understand your reply at all..... ????

 

[ghudson]

Searching the forum is a great way to discover and learn the answers to your Access programming questions.

Browse [Find a directory or file]

 

[greaseman]

ghudson,

Thanks for your link to your example coding..... I'll check it out tomorrow when I'm at work.

And, before I posted my question, I did do a search of the forum, using keywords such as "open file," "file open", "save," "save as." "dialogs," and the like. I always try a search prior to posting a question or issue.

In any case, thanks for replying..... I really appreciate it. Have a great evening and a great day tomorrow

 

[greaseman]

ghudson,

Your sample database is very nice. However, what I'm really interested in is something that'll let me specify the name of an output Excel file prior t actually creating the output Excel file. I other words, my project will not have a previously created file upon starting it; I want my users to be able to tell the application what they want to call the output by typing in something.

Is my question / thought making any sense?

Thanks again, and in advance.

 

[ghudson]

Then give them a text box to key the file name into and incorporate that with the "browse directory" funtion in my sample to allow the user to choose the file with the name of their choice in a directory that exists buy forcing them to "browse" and select the directory of their choice. You will have to do a lot of error checking to ensure the file save action is valid just incase the user make a bad [illegal] choice with their file name.

 

[greaseman]

ghudson,

Thanks! Sometimes the obvious things are hidden in "the forest for the trees."

Have a good day!

 

[ghudson]

your are welcome

 

[greaseman]

ghudson,

That still won't work for what I have in mind..... I'm thinking I probaby didn't explain myself clearly, so I'll try again......I've got an application that exports Access data to Excel. It works beautifully, with one little exception..... I have to have an empty Excel worksheet created first that I then must choose by clicking on it in a File Open dialog prior to running the application.

What I want ideally is to have a box open on my main form that lets me enter the name of my Excel workbook to export to, have my program create the empty Excel workbook using the name I just entered, export my report to the worksheet, and then close and save the newly created Excel report worksheet.

I have looked high and low, but just can't seem to ifnd the code or help that might allow me to do this.

I've also tried Dev Ashis' "API: Call the standard Windows File Open/Save dialog box, but although I am able to key in a file name to save to, when I exit my application, I don't see the Excel file I just named.

Hopefully, I've ben a bit clearer on this go 'round.

Thanks! I appreciate your thoughts!

 

[ghudson]

You can easily use the TransferSpreadsheet method "just" to create an excel file. Then you can use your method to export your data.

Otherwise you will have to dig into Excel automation. I have never had to do what you are trying so I can not help you any further. I have only had to modify Excel files from Access [and I have a few posts in this forum on how to do it]. If you are only stuck on how to create a brand new Excel workbook from Access then that code has to be around here somewhere. Search around for there are a lot of posts on how to work with Excel from Access within this forum and also at UtterAccess.com

Post back once you figure it out so others can learn from your journey.

Good luck!

 

[greaseman]

I'll give it a go, and yes, I've also been looking through UtterAccess.

Thanks!

 

[greaseman]

I got it figured out!!

ghudson,

I figured it out, with your and Rob Denham's help! I cobbled together a sample .mdb database (Access 2000) and will try to attach it to this reply. The sample is deliberately incomplete in places, to protect some proprietary data. However, if you "walk through" the code, it should be self-explanatory. What my sample does is to allow you to pick desired folder for your output Excel file, then pick a date to append with the Excel file's name, and then export your formtted Access report to Excel, with the same formatting.

Ig anyone has questions or comments, please PM me. Also, if the file is too large to be attached t othis reply, please PM me and I'll e-mial it to you.

I'm a happy camper! Thanks to all who assisted me with this headache! And special thanks to ghudson and Rob Denham for the wonderful assistance!

 

[greaseman]

Sorry for my typos in my previous reply..... they hired me for my programming, not my typing or English!

 

[ChrisGow]

Your problem has helped me out doing my database...Im not sure if the calendar that is on the form is your or a built in function, but I am going to borrow it

 

[gmmccarthy]

my simple solution

What I have done for this problem is to create a file named BOLTemplate.xls in a certain location. It has the report format on the first sheet with cells linked to a hiddend sheet.

This hidden sheet is created when the user runs the transferspreadsheet macro to export the data. By exporting it to the BOLTemplate.xls file it simply overwrites the hidden sheet therefore refreshing the report.

You then save it wherever you need it from Excel.