Query to Excel

[Freckles]

I am using Access 97 and Excel 97. I have a regularly updated database that I use the output to macro to send various queries to excel for distribution to those who are "not comfortable with"/"afraid of"/stubborn regarding Access. Since this is so regular, I would like to automate the procedure as much as possible. So what I think I would like is a procedure to do the following (I don't know/understand modules):

Something that will open the newly created Excel file (always the same name), select all, remove Word Wrap and format column width and row height, then save the file with the same name in Excel 97? All without my intervention so it can be run a night? : Deb Koplen
deb.koplen@verizon.com

A person can stand almost anything except a succession of ordinary days.

 

[link9]

You can open an Excel application from Access -- best way to do this would be to write a module (I know you said you don't understand them, but now might be a good time to learn).

dim ExcelApp as Object
dim myPath
myPath = "c:\yourPath\yourDirectory\theFile.xls"
set ExcelApp = getObject(myPath, "Excel.Sheet&quot.ActiveSheet

The above will get the object into memory so that you can perform functions on the object (such as the stuff you have indicated above) --

The best way to figure out how to select all, remove word wrap, etc... is going to be to go into the actual excel file and record yourself a macro that does exactly what it is that you want to do --

Once that is done, you can open up the vba editor and copy and paste the code into your Access application -- all methods will have to be preceded by the ExcelApp variable name. i.e. -- ExcellApp.ActiveSheet.Select("A1:Z100&quot would select the range, A1:Z100 on the active sheet of the workbook --

You will see once you record the macro --

Once you perform what you want, you can save the file like this:

ExcellApp.SaveAs FileName:="c:\yourPath\yourDirectory\yourFileName.xls"

I'm sure that this has a couple warts on it, but it's definitely the general gist of what is going to have to be done... and I hope it helps out.

Paul Prewett

 

[EhWot]

Hey Link9,

I've been struggling to make GetObject work properly, that snippet of code was clean, concise and exactly what I needed. Thanks for posting it!