Formatting exported Excel spreadsheets within Access VBA macro

[Monterey_Manzer]

Hi Everyone,

I've been reading through the various posts on this topic and while there is some similar stuff, nothing that I could directly apply. I know this has to be fairly simple, I'm just running into difficulties and I can't seem to find the necessary reference material. So here is my situation.

The Situation: My office utilizes an Access db to track administrative processes that I maintain but we have some remote staff who cannot use this system. For them we routinely export Access reports to Excel spreadsheets and transmit them via email so these remote staff receive regular updates. As many have discussed, the various Export to Excel options produce a spreadsheet with very little of the lovely formatting present in the Access report. I don't mind as I developed a macro in Excel that actually produces a format more custom tailored to those remote staff members. However, I am trying to both empower my coworkers and disperse some of the responsibilities with regard to this formatting. I tried copying and loading the Excel macro into each employee's copy of Excel but that has proved way to time consuming and not appreciated by the coworkers.

My Desired Solution: I want to write the formatting macro I have in Excel VBA into the Export to Excel macro I have triggered by a button on the header of the report. I am just having trouble translating the Excel VBA to Access. Before anyone asks, I have activated the MS Excel 14.0 Object Library. Below is the code for the Export to Excel button on my form:

Private Sub Excelbtn_Click()
Dim M As Long, D As Long, Y As Long
Dim xlApp As Excel.Application, oWB As Excel.Workbook, oSheet As Excel.Worksheet
On Error GoTo Excelbtn_Click_Err
    
    M = Month(Date)
    D = Day(Date)
    Y = Year(Date)
    fName = "Graduation and Hiring Tracker  - Test " & M & D & Y & ".xls"
    fPath = "\\comfort\SMART$\SMART\COHORT ADMIN\TRACKERS\Graduation and Hiring Trackers\" & Y & "\"

    
    DoCmd.OutputTo acOutputReport, "Grad and Hire Report", "Excel97-Excel2003Workbook(*.xls)", fPath & fName, True, "", , acExportQualityPrint

    
    Call GandHFormat(fName, fPath)

The GandHFormat is a sub in a module where I hope to construct the macro. It doesn't do anything right now but I figured I would need to have the file path and name so I made those necessary inputs. If I can get this working I plan to build similar macros into all reports we may be asked to provide in spreadsheet form.

Below is the macro I am trying to translate:

' GradHire Macro
' Formatting Graduation and Hiring Tracker output for distribution.
'
' Keyboard Shortcut: Ctrl+h
'

    Range("A1:X1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
        .Borders.LineStyle = xlDouble
    End With
    ActiveCell.FormulaR1C1 = _
        "SMART Graduation and Hiring Tracker For " & Date & ""
    Rows("1:1").Select
    Selection.Font.Bold = True
    Selection.Font.Size = "16"
    Selection.RowHeight = 24
    Range("A1:X1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    
    Columns("B:B").EntireColumn.AutoFit
    Columns("B:B").ColumnWidth = 30
    Columns("C:C").ColumnWidth = 34
    Columns("D:W").ColumnWidth = 12
    Rows("2:2").WrapText = True
    Rows("2:2").EntireRow.AutoFit
    Range("A1:X1").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    For i = Selection.Rows.Count To 1 Step -1
        If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
            Selection.Rows(i).EntireRow.Delete Shift:=xlUp
        End If
    Next i
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Set myRange = Columns("A:X")
    RowCount = Application.WorksheetFunction.CountA(myRange)
    Debug.Print RowCount
    Range("A2:X282").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$2:$X$282"), , xlYes).Name = _
        "Table1"
    Range("A2:X282").Select
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium16"
    ActiveWindow.SmallScroll Down:=-18
    ActiveWindow.SmallScroll Down:=60
    Range("B3:B277").Select
    Range("B277").Activate
    ActiveWorkbook.Worksheets("Grad and Hire Report").ListObjects("Table1").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Grad and Hire Report").ListObjects("Table1").Sort. _
        SortFields.Add Key:=Range("B2:B277"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Grad and Hire Report").ListObjects("Table1"). _
        Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   Rows("3:283").Select
    Selection.RowHeight = 28
   Range("B283:B283").Value = "Total number of graduation participants for " & Year(Date)
   With ActiveWorkbook
    .Save
    .Close
End Sub

I generated this code by simply recording the macro while I applied the desired formatting in Excel. There are probably some repetitive code snippets I could get rid of. Regardless, I would really like to get this macro into an Access module and I'm just not sure how to get started. If anyone could help me figure out how to get started I would greatly appreciate it.

Thanks!
Ryan

[JoeM]

If you already have the code you need in an Excel macro, why not just keep that Excel Macro, and call it from Access VBA?
Here is a link on how to do that: http://datapigtechnologies.com/blog/...xcel-workbook/
There are many others out there.

[Monterey_Manzer]

Hi Joe,

Thanks! I actually tried that earlier but the problem was I had to get copies of my macro into each user's version of Excel (they are not savvy enough to get it there themselves). Most did not appreciate having me monkeying around with their files every time I figured out a nifty update that I wanted to apply so I am trying to get it into the front end Access dbase I regularly update. Otherwise it's a pretty damn nifty little trick.

[JoeM]

Why do you have to give each user their own version of the Excel macro?
Why not just place it somewhere on the network in a public place and have Access call that?

[Monterey_Manzer]

I guess I'm not sure how to do that either. I've only ever written macros and called them from my Personal workbook. I wouldn't know how to go about calling a network macro.

[JoeM]

The link that I provided shows you how to do that. Essentially, all the Personal Macro Workbook is is a hidden Excel file that opens automatically anytime you open an Excel file on that computer. So, likewise, all you need to do is to open the Excel workbook containing the macros, and run them. The link I provided shows you how to perform those two steps.

The only other thing you may have to deal with is Macro Security settings. If set to Medium or High, the Excel macro probably won't run automatically unless you sign the Macros or use Trusted Locations. Either way, each user's computer will need to be set up to Trust the Signature, or have Trusted Locations. It is just a one-time thing that has to be done. Using Trusted Locations is probably the easiest route. All that does is tell the particular computer to trust any macros in a specified location on your network.

Here is a link on how to set Trusted Locations. It is very easy. http://office.microsoft.com/en-us/ex...31999.aspx#BM3

[Monterey_Manzer]

Okay, so I guess now my only question is this. How do I go about opening the network stored workbook that contains the macros while running the macro on the existing spreadsheet? If I include the xl.Workbooks.Open("Macro containing workbook") code then both will be open. Would this simply require some sort of Select statement to select the spreadsheet I want to format?

Thanks for the help, this is really good stuff!

[JoeM]

Typically, what I do is have my Access database export the file to a "statically" named Excel file.
Then, have your Excel macro open this "statically" named Excel file (since the path and name is static, you can hard-code it into macro), perform the formatting, and then save it (I typically save it with a date/time stamp so that the final file has a unique name).

[Monterey_Manzer]

I must have expressed myself poorly. I have no problems with the naming of the exported spreadsheet. Our naming conventions are such that each day the file is exported it automatically gets a unique, and more importantly predictable, file name. This can be found in the first code snippet I presented. So I know what the file name of the exported spreadsheet will be.

Rather, I am wondering how I select that spreadsheet as the active one that macro will act upon if I am opening the macro containing workbook as part of the subroutine? To try and highlight this I will use my Export to Excel code and add the code from the Datapig posting.

Private Sub Excelbtn_Click() Dim M As Long, D As Long, Y As Long 
Dim xlApp As Excel.Application, oWB As Excel.Workbook, oSheet As Excel.Worksheet 
On Error GoTo Excelbtn_Click_Err          
     M = Month(Date)     
     D = Day(Date)     
     Y = Year(Date)     
     fName = "Graduation and Hiring Tracker  - Test " & M & D & Y & ".xls"     
     fPath = "\\comfort\SMART$\SMART\COHORT ADMIN\TRACKERS\Graduation and Hiring Trackers\" & Y & "\"      

     DoCmd.OutputTo acOutputReport, "Grad and Hire Report", "Excel97-Excel2003Workbook(*.xls)", _
                                                   fPath & fName, True, "", , acExportQualityPrint <- Results in an open spreadsheet with file name = fName
    
' Begin DataPig code:
    Set xlApp = CreateObject("Excel.Application")

    xlApp.Workbooks.Open("NetworkDrive\macroWorkbook")  <- Opens the workbook containing the formatting macro

    xLApp.Visible = True

    xLApp.Run("MyMacro") <-How do I guarantee this operates on the spreadsheet I just exported instead of the empty Workbook that contains the macro?

I hope that clarifies my question. It seems to me that there must need to be some sort of "Select" statements between the xlApp.Visible = True statement and the running of
the macro. I could be, and often am, wrong but that's just what occurs to me.

Thanks!
Ryan

[JoeM]

No, I understood what you were saying, but perhaps I wasn't clear in what I trying to instruct you to do. But the fact that you posted your code will help me clarify it.

There are two ways to go about this. Both involve your "fname" variable where you are naming your output file.

Option 1
I see that you have the datestamp there. Don't use that here - give your file a static export name so that it is the same every single time (i.e. "MyExport.xls").
Since you always know what your Excel file will be named, program your Excel Macro to open that exact file name (hint: you can create Excel Macros that run on other files, just have the first step of that macro open that file - that code can easily be obtained using the Macro Recorder).
Then, after formatting, have the Excel macro save the file name with the datestamp in it.

Option 2
Keep what you have there so far, leaving the datestamp in your export file name. You can mirror the same logic build the same file name in your Excel macro, and have your Excel macro open that dynamic file name. Then just apply your formatting, and save as is with the same file name.

They key is what you put into your Excel macro. Basically it should contain these steps:
1. Open the Excel file you just exported from Access (the file name can easily by determined by the options mentioned above).
2. Apply your Formatting.
3. Save the Excel file.
4. Close all your Excel files.

Does that help clarify things? So essentially, your Access VBA code is calling your Excel macro and letting it do a lot of work in finishing up the job.

[Monterey_Manzer]

Thanks for your suggestions. I have definitely progressed and am now encountering all new problems. I will share the code I have so far and describe the issues

First, I created the workbook containing the macro and placed it on a network drive. I have begun building my macros there and I just have a couple final issues (I think). The Access code is roughly the same as I posted above. One problem I encounter is that each time Access opens the MacroBook Excel file, I am told that it is locked for editing by me. Do I need to modify the command? I just use the following :

xlApp.Workbooks.Open ("\\comfort\SMART$\SMART\(SMART) Access Databases\Excel Macros\MacroBook.xlsm")
xlApp.Visible = True
xlApp.Run ("GradHire")

Is there some modifier I can add to the .Open syntax that will override this?

My second problem occurs with the Excel macro that is called. It's pretty basic since I'm just using a few commands to test it out. The Excel VBA is shown below:

Sub GradHire()
' Formatting Graduation and Hiring tracker Excel export

Dim D As Long, M As Long, Y As Long, myDate As String, fName As String
Dim i As Long, LastRow As Integer, myRange As Range, LastCol As Range

' First we will get the necessary details to make this work
    D = Day(Date)
    M = Month(Date)
    Y = Year(Date)
    myDate = M & D & Y
    'Debug.Print myDate
    
    ' Getting the file name, which includes the file path
    fName = "\\comfort\SMART$\SMART\COHORT ADMIN\TRACKERS\Graduation and Hiring Trackers\" _
        & Y & "\Graduation and Hiring Tracker " & myDate & ".xls"
    'Debug.Print fName
    
' Now we open the tracker we wish to format
    Workbooks.Open(fName)
    
    LastRow = Range("A1").End(xlDown).Row
    
    Debug.Print LastRow
    
    With ActiveWorkbook
        .Save
        .Close
    End With
End Sub

The problem that occurs here is this code results in two instances of the spreadsheet I am trying to format being open at the same time, each one overwriting the other. The rest of the code works just fine. I have tried using the Workbooks(fName).Activate command but I am told the Subcript is out of range, even though I have seen examples of users utilizing the filename to activate specific workbooks/worksheets. If you have any further suggestions I would greatly appreciate it.

Thanks!
Ryan

[JoeM]

Both issues could be related to the same thing. It sounds almost as if the Excel workbook with the macros is being opened twice. That could cause both the issues you discussed.
Can you go back and double-check and confirm that whatever process in Access that kick this off is being called twice?

[Monterey_Manzer]

You must have been correct. I made a small modification to my Access code and a minor tweak to the Excel macro to fix everything.

With the Access code, I was already creating an instance of the Excel Application with the DoCmd.OutputTo command. So when I used xlApp=CreateNew("Excel.Application") we had two instances of the program running rather than one with multiple workbooks. I fixed this with the following code:

' Activating the Excel workbook where our formatting macros are stored
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Workbooks.Open ("\\comfort\SMART$\SMART\(SMART) Access Databases\Excel Macros\MacroBook.xlsm")
    xlApp.Visible = True
    
    M = Month(Date)
    D = Day(Date)
    Y = Year(Date)
    fName = "Graduation and Hiring Tracker " & M & D & Y & ".xls"
    fPath = "\\comfort\SMART$\SMART\COHORT ADMIN\TRACKERS\Graduation and Hiring Trackers\" & Y & "\"

    
    DoCmd.OutputTo acOutputReport, "Grad and Hire Report", "Excel97-Excel2003Workbook(*.xls)", fPath & fName, True, "", , acExportQualityPrint
    
    xlApp.Windows("MacroBook").Activate
    xlApp.Run ("GradHire")

The second tweak was that, again thanks to the DoCmd.OutputTo command, I had the spreadsheet I wanted to format already open. If you look at my earlier Access code you will see I tried to use the Workbooks.Open(fName) command to make that one the active window. This is what created the two copies of the spreadsheet. I tweaked my Excel code, using the same code to get my file name (fName) variable, so that now it uses the Windows(fName).Activate method instead. These two tweaks have solved my problems! Thanks so much for all the help with this! Now if Access could only save in .xlsx format, my users wouldn't have to click a thing after they export the report.

Thanks again!

[ssanfu]

I didn't want to but in while you were working with Joe and confuse the issue, but you can do the Excel formatting directly from Access.
If you are interested, I could post the code.

It was interesting to find another way to format a worksheet. Thanks.

[Monterey_Manzer]

I wouldn't mind. I think it's a good idea to have multiple avenues for reaching the same goal. You never know what kind of restrictions you might wind up with and in that event it's handy to have a couple different ways to do something. Please post away!

Thanks!
Ryan