I don't trust your code.
The first problem I see is that the DoCmd.OutputTo statement doesn't
necessarily create an Excel worksheet, or indeed any file at all (the
user can choose another format, or simply press Cancel).
The second is that you seem still to be using your original
FormatPageEventTracking() procedure (because you are not passing a
filespec as required by the modified FormatPageEventTracking() I posted.
As I said before, you cannot rely on your code to get hold of the right
instance of Excel, the right workbook or the right worksheet.
I suspect that what's happening may be that the DoCmd.OutputTo statement
returns control to your code as soon as (maybe even before) the new
workbook file is written to disk , and therefore before (or potentially
before) there has been time for Excel to load itself and open the
workbook. As a result, FormatPageEventTracking() may be calling
appXL.ActiveWorkbook.ActiveSheet.Cells.Select
before there is an ActiveWorkbook. If you step through the code, there'd
be time for the workbook to open before this call.
If I'm right, the solution is to code it properly. Get the filespec for
the .xls file from the user and pass this to DoCmd.OutputTo *without*
the Autostart argument to create the .xls file without opening Excel.
Then pass the filespec to the modified FormatPageEventTracking() and let
this launch Excel, format the worksheet, save it, and finally close
Excel.
Thanks again - but no luck. Here is the procedure that calls
formatpageeventtracking:
Private Sub cmdEvent_Click(lsOption As String)
On Error GoTo cmdEvent_Click_err
If lsOption = "Excel" Then
DoCmd.OutputTo acOutputReport, "rptEventTracking", , , True
FormatPageEventTracking
Else
DoCmd.OpenReport "rptEventTracking", acViewPreview
End If
Exit Sub
cmdEvent_Click_err:
If Err.Number = 3078 Then
MsgBox "You need to first link the Excel files."
fncATTACH_TABLES "Entire Spreadsheet"
fncATTACH_TABLES "Events"
MsgBox "The files are now attached."
Exit Sub
Else
MsgBox Err.Description
End If
End Sub
John Nurick said:
This sounds like a timing issue. I'd try inserting
DoEvents
after the GetObject call and before
Set raR = wbkW.Worksheets("Sheet1").Cells
If that doesn't fix it, please post the code that calls
FormatPageEventTracking()
Thanks again,
I tried your suggestion and still get the error, however I noticed
that
when
I put a break point at the beginning of the procedure and step through it,
it runs fine every time. Does this make any sense?
It's usually best to avoid using the Selection object when automating
Office applications and instead, use something like
Dim appXL as Excel.Application
Dim raR as Excel.Range
...
Set raR = appXL.ActiveWorkbook.ActiveSheet.Cells
With raR
blah blah
End With
But one of the circumstances in which you'll get the error you've been
having is if you refer to ActiveWorkbook when the instance of Excel
doesn't have a workbook open. The problem with
GetObject(,"Excel.Application") is that it grabs the first instance of
Excel it finds.
Maybe something like this is what you need, to ensure you get the right
workbook. Pass the name and location of the workbook to the procedure:
Sub FormatPageEventTracking(FileSpec As String)
Dim wbkW As Excel.Workbook
Dim raR as Excel.Range
On Error Resume Next
Set wbkW = GetObject(FileSpec)
If wbkW Is Nothing Then
MsgBox "Couldn't open " & Filespec, _
vbExclamation + vbOKOnly
Exit Sub
End If
On Error GoTo 0
Set raR = wbkW.Worksheets("Sheet1").Cells
With raR
blah blah
End With
wbkW.Save
Set raR = Nothing
Set wbkW = Nothing
End Function
Hi,
Thanks for the help. I have gotten as far as writing the following
procedure. When this procedure runs the Excel worksheet is already open.
The
problem is sometimes this code runs fine, and for some reason other times
I
get an error '91 object or with block variable not set' on the line
below
that I have put an asterisk next to. I don't understand why I'm getting
this
since Excel is running and the workbook is open. I want to keep the
workbook
open when the function ends rather than close excel. Any ideas
would
be
appreciated
Function FormatPageEventTracking()
Dim appXL As Excel.Application
Set appXL = GetObject(, "Excel.Application")
appXL.ActiveWorkbook.ActiveSheet.Cells.Select '** error occurs here
With appXL.Selection.Font
.Name = "MS Sans Serif"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
Set appXL = Nothing
end function
Hi Rasta,
You can use Automation to manipulate the workbook with Excel VBA code
running in your Access database. These links show the basics:
http://www.mvps.org/access/modules/mdl0006.htm
http://support.microsoft.com/?id=210148
Hi,
I have some code that runs an Access report and then exports that
report
into Excel. I need to format some of the rows in the newly created
Excel
file (they need to be bold print etc). Is there some way I can
do
this
from
inside Access, I'm not sure how it could work since the file is being
created from the export so I cannot have an Excel macro that
will
run
from
within the new file - I hope that makes sense
