How can I run a macro for Excel in Access?

[Briansmi1116]

Hi all,

I am using Access 2003 as well as Excel 2003.

I have an Access database that has an output to Excel button I created. This outputs a form to an Excel file A. I then have Excel file B formatted and linked to A. I would like to do this without the link, where when I export to Excel, it automatically formats the Fields. If I run the macro in Excel to format on open, every time I export the file it deletes the macro. So this would be something I need to run in Access. The file is on the network for everyone to use, so I need a way to do this without going to everyones computer to make it work.

Thanks for your help in advance.

Brian Smith

[nico5038]

My approach would be to create a query with the proper formatting for Excel.
You can use the Format() function for most cases.

What's the specific need in formatting the fields ?

Nic;o)

[Briansmi1116]

I am wanting to have all the information auto fit, freeze panes, and save with a filename with date and time. I want to be able to do this time after time, and be able to open Excel without running the macro. Right now, I am using an Excel file, with the macros I need for formatting, to do this. I would like to get rid of that file to save space for Excel file being created. How do I do this in a query?

[nico5038]

Sorry, but freezing panes, etc. can't be done using a query.
This will require "automation" and some VBA coding against the Excel object model....

How well are your coding skills ?

Nic;o)

[Briansmi1116]

Not very good, I am learning everything I have done off the net, and doing some manipulating to make it work for me. I can show you what I have so far in Access and in Excel.

Access:

Private Sub excel_Click()
On Error GoTo Macro1_Err

DoCmd.OutputTo acForm, "Issues lookup", "MicrosoftExcelBiff8(*.xls)", "L:\~Public\DataBase\Sustaining Engineering\Sustaining DB Export " & Format(Now(), "mm-dd-yy hh_mm_ss AMPM") & ".xls", True, "", 0

GoTo Skip_Err

Macro1_Err:
MsgBox Error$

Skip_Err:
Dim xlswkb As Object

Set xlswkb = CreateObject("Excel.Application")
Set xlswkb = GetObject("L:\~Public\DataBase\Sustaining Engineering\Do not Rmove - Jim A 10-1-07\issues.xls")

xlswkb.Application.Run "issues.xls!formatting"


End Sub


Excell:

Sub formatting()
Cells.Select
Selection.Rows.AutoFit
Selection.Columns.AutoFit
Range("B3").Select
ActiveWindow.FreezePanes = True
Range("A1").Select
ActiveWorkbook.Save
End Sub


This is how I get it to do what I want. Now the Access just goes to this Excel form to just run the macros, and thats all. It creaqtes a diffrent Excel form to do the macros in.

[nico5038]

Basically the Excel macro code resembles the needed VBA code (and can be reused) to customize to your needs.
Best to start with the piece of code the questioner posted in http://www.thescripts.com/forum/thread668084.html

Nic;o)

[Briansmi1116]

The link is not working, it says page not found.

[nico5038]

Sorry, forgot the ".html", try again :-)

Nic;o)

[Briansmi1116]

Modified the VBA to try to work with my Databse, and get a compile error on "Dim as..." parts. Any ideas?