Reference a Specific Excel Book

Question

Experts -

I have some code here that formats an Excel book after export.  Works great except if there are already other Excel files open, then it breaks.

As such, how do I reference the specific Excel workbook I want to format?

Thank you!

---

Bonediggler

Answer 1

What is the error message? Can you post the code? Which line is highlighted?

Answer 2

Can you show us your code?  How are you performing the export?

---

Daniel Pineault, 2010-2017 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 3

Public Function f_Export_Report()

On Error GoTo ErrorHandler

Dim sPath As String
Dim sDate As String
Dim xl As Excel.Application
Dim xlBook As Excel.Workbook

sPath = “\\Folders\”
sFilenm = "File_Name.xlsx"

'Shape data, export report
DoCmd.SetWarnings False
DoCmd.OpenQuery "q1"
DoCmd.OpenQuery "q2"
DoCmd.OpenQuery "q3"
DoCmd.TransferSpreadsheet acExport, 10, "q4”, sPath & sFilenm, True

'Format Excel output
Set xl = CreateObject("Excel.Application")

Set xlBook = GetObject(sPath & sFilenm)
xl.Visible = True
xlBook.Windows(1).Visible = True

'Format sheet
Set xlsheet1 = xlBook.Worksheets(1)
With xlsheet1
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A1:F1").Select
    Selection.Font.Bold = True
    Range("A2").Select
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
    Range("A1:F1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    Range("A1").Select
    ActiveWorkbook.Worksheets("q4").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("q4").Sort.SortFields.Add _
        Key:=Range("A2:A46"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("q4").Sort.SortFields.Add _
        Key:=Range("C2:C46"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("q4").Sort.SortFields.Add _
        Key:=Range("D2:D46"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("q4").Sort
        .SetRange Range("A1:F46")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With

'Clean up
xlBook.Close True
Set xlBook = Nothing
xl.Quit
Set xl = Nothing

'Send email
Call SendNotes(sFilenm, sPath)

MsgBox "Report Exported and Emailed!"

Exit_Sub:
Exit Function

ErrorHandler:
MsgBox Err.Description, vbCritical
Resume Exit_Sub

End Function

---

Bonediggler

Answer 4

Hi,

Thanks for posting the code. When you said "it breaks," what is happening? Are you getting an error message? If so, what does it say? If you Debug, which line is highlighted as the cause of the error?

Answer 5

I imagine the problem is with these lines here:

Set xl = CreateObject("Excel.Application")

Set xlBook = GetObject(sPath & sFilenm)

You should open your workbook in the xl Application you've created; then there won't be any confusion with other instances of Excel.  Try this:

    Set xl = CreateObject("Excel.Application")
    Set xlBook = xl.Workbooks.Open(sPath & sFilenm) 

I'm not sure why you have code (not quoted) to make the Excel instance visible, since you are only formatting it and then closing it.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html