How do I open an Excel template (xlt) from MS Access?

  • Thread starter Thread starter Keith G Hicks
  • Start date Start date
K

Keith G Hicks

First, please don't suggest using export to excel. I need a much nicer
presenentation than what that does.

I've used some code for some time now that opens Excel and creates a nice
file using vba automation from MS Access. But it's really slow. All the
formatting takes forever (column widths, font settings, etc.). But it works
fine. It just takes a minute or 2 to create a file with only a couple
hundred rows in it.

So it occurred to me to use templates already set up and formatted for each
specific output my client needs. I already do that with Word automation from
Access and it's much faster than creating files from scratch. I figrue this
will improve speed immensely.

The problem is that I cannot get my code to work that opens the template.
Here's what I have:

Option Compare Database
Option Explicit

Dim objExcelApp As Excel.Application
Dim objExcelBk As Excel.Workbook
Dim objExcelSht As Excel.Worksheet

Public Sub subOpenExcel(sExcelTemplate As String)

On Error Resume Next
Set objXLApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set objXLApp = CreateObject("Excel.application")
objXLApp.Visible = true
objXLApp.WindowState = wdWindowStateMaximize
End If

sExcelTemplate = sExcelTemplatesPath & sExcelTemplate
objExcelApp.Workbooks.Add Template:=sExcelTemplate

Set objExcelBk = objExcelApp.ActiveWorkbook
Set objExcelSht = objExcelBk.Worksheets(1)

End Sub

Each line runs and the code runs without errors but there are soem proboems:

1. The Excel window never maximizes
2. the objExcelBk is "nothing"
3. the objExcelSht is "nothing"

So that when the code that calls subOpenExcel tries to reference objExcelSht
like this:

objExcelSht.Name = "some new name"

I get the error "object or with block not set..."

Can anyone tell me specifically what I've done wrong above?

Thanks,

Keith
 
Never mind. I found the hicup. I needed the objExcel in there for some
reason. Not sure why but it solves the problem.

dim objExcel as object

On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set objExcel = CreateObject("Excel.application")
objExcel.Visible = bDev
objExcel.WindowState = wdWindowStateMaximize
End If

Set objExcelApp = objExcel.Application

sExcelTemplate = sExcelTemplatesPath & sExcelTemplate
objExcelApp.Workbooks.Add Template:=sExcelTemplate

Set objExcelBk = objExcelApp.ActiveWorkbook
Set objExcelSht = objExcelBk.Worksheets(1)


Keith
 
Back
Top