Excel Application Property using in Access VBA

Question

I want to generate and format an excel workbook out of access. The creation of the file is done easy, but I struggle with the format.

file creation Dim strCurrentDBName As String

strCurrentDBName = CurrentDb.Name
For i = Len(strCurrentDBName) To 1 Step -1
   If Mid(strCurrentDBName, i, 1) = "\" Then
      strPath = Left(strCurrentDBName, i)
      Exit For
   End If
Next
xlsxPath = strPath & "Report.xlsx"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Report", xlsxPath, True

MsgBox ("Report generated. " & xlsxPath)

format

Dim xl As Object
'This deals with Excel already being open or not
On Error Resume Next
Set xl = GetObject(, "Excel.Application")
On Error GoTo 0
If xl Is Nothing Then
  Set xl = CreateObject("Excel.Application")
End If

Set XlBook = GetObject(xlsxPath)
'filename is the string with the link to the file ("C:/....blahblah.xls")

'Make sure excel is visible on the screen
xl.Visible = True
XlBook.Windows(1).Visible = True
'xl.ActiveWindow.Zoom = 75

'Define the sheet in the Workbook as XlSheet
Set xlsheet1 = XlBook.Worksheets(1)

'Format
With xlsheet1
    xlsheet1.Rows("1:1").Select

and here is my error (Run-time error '1004': Application-defined or object-defined error)

    xlsheet1.Range(xl.Selection, xl.Selection.End(xlDown)).Select
    xlsheet1.Selection.EntireRow.AutoFit

End With

Erik A · Accepted Answer · 2017-12-16 22:02:21Z

1

You're using the xlDown enum value, which requires a reference to the Microsoft Excel Object Library. Since you're using late bindings, that reference probably isn't set.

Work around it by using the value of xlDown, -4121:

xlsheet1.Range(xl.Selection, xl.Selection.End(-4121)).Select

Note that this error would've been more easy to spot if you had put Option Explicit at the top of your module.

answered Dec 16, 2017 at 22:02

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel Application Property using in Access VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related