INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Running an Excel Macro from Access

Running an Excel Macro from Access

(OP)
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.

RE: Running an Excel Macro from Access



hi,

Quote:

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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Running an Excel Macro from Access

(OP)
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).

RE: Running an Excel Macro from Access

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

RE: Running an Excel Macro from Access

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close