Access and Excel

[mr moe]

I have an access database, I'm exporting data to excel and then formatting the data thru a macro in excel the problem is that when ever I export the data to excel the new file replaces the old file which then deletes the macro in excel. Any suggestions on what to do?

 

[HiArt]

I do something similar, but as I need to distribute the spreadsheets within my organisation I decided that I didn't want to distribute the macro code as well.

Therefore I got excel to create a new spreadsheet and wrote the data to that. That way you can keep the spreadsheet with the macro code seperate and it won't need to be overwritten.

Hence I have a 'Weekly Schedule Report (Update).xls' with the code which opens 'Weekly Schedule Report.xls'. Something like this:

strFilename = "c:\Weekly Schedule Report.xls"
Workbooks.Open Filename:=strFilename, _
AddToMru:=True
Workbooks("Weekly Schedule Report.xls").Activate
ActiveWindow.FreezePanes = False
Cells.Select
Selection.Delete Shift:=xlUp

Hope this helps