Running an Excel Macro from Access

[GibFinch]

IS it possible to run an Excel macro from Access? I am trying to export data from Access to a spreadsheet, reformat it and save it using a different extension type (either .dat or .prn, both of which will save the formatting needed). I have an Excel macro that does the formatting, but have no way to start the macro without opening the spreadsheet and I'd rather keep the users out of the file for security reasons. Thanks in advance.

 

[SkipVought]

hi,

either .dat or .prn, both of which will save the formatting needed

Both of those file types are text files. Text files can contain no formatting. Any Excel workbook/Sheet saved as any kind of a text file, looses all formatting

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[GibFinch]

Sorry, should have been more specific. When I make the format changes in Excel, I then save it as a .xls then change the file extension to .dat and/or .prn and the changes in formatting DO stay. I have been doing this for quite some time because our business system requires either one of these formats to transfer data in (along with predetermined column widths). My question is how to or can you run an Excel macro from Access so my users don't have to open the spreadsheet (again, for security purposes).

 

[Revolution1200]

You could transfer the macro code into access and use the following to open the spreadsheet


Dim mainWorkbook As Excel.Workbook
Dim currentWorksheet As Excel.Worksheet

myFileName = "O:\GTS\TechServices\Telephony\Call Stats\" & "Mailbox List " & runDate & ".xls"

Set mainWorkbook = Workbooks.Add

mainWorkbook.SaveAs fileName:=myFileName

Set currentWorksheet = mainWorkbook.Sheets("Sheet1")

...

Stuart

 

[GibFinch]

Thanks for the reply Stuart. To clarify, I had no problem creating or opening the Excel file in Access, just the reformatting and saving in text format. I did figure out the solution earlier this week, so everything is working correctly now. Again, thanks for the help.