Subject: New problem: Object variable not set. New info!
Thanks to all your help, and that of Dan P King, we are nearly there!
Code is now working, but only with LS Debugger on.
Debugger ON - everything works as I planned it, the column I wanted gets formatted a-OK!
Turn off the debugger, and I am getting the Illegal Function call. I can’t tell where! as the error only happens when the debugger is off!
However, I presume it is something to do with the way Excel is called by the program ??
Following code is OK when debugger ON! But gives error when debugger OFF of ‘Illegal Function Call’
Sub Initialize
’ FormatMacro Macro
’ Macro recorded 22/02/2007 by 3M
’
’
Dim Excel As Variant
Dim Exceldoc As Variant
Activateapp "Microsoft Excel"
'Set Excel = CreateObject("Excel.Application") 'Create Excel object
Set Excel = GetObject(,"Excel.Application")
Set Exceldoc = Excel.ActiveWorkbook 'Get a handle for the active document
Const xlrange =2
Const xlColumns=2
'Const Columns=2
Const xlGeneral=1
Const xlBottom=-4107
Const xlHorizontal=-4128
Dim Range As Variant
Dim Columns As Variant
Columns = xlColumns
'Excel.Application.Columns("M:M").Select
' FormatMacro Macro
Subject: RE: New problem: Object variable not set. New info!
Are you sure that it is fixed now?
The Illegal function call is at the line: Activateapp “Microsoft Excel”
You got to replace it with:
Set Excel = CreateObject(“Excel.application”)
If Excel Is Nothing Then
Msgbox "Error!!! Agent was unable to open Microsoft Excel."
Exit Sub
End If
Excel.Workbooks.Add
Set Exceldoc = Excel.Workbooks(1).Worksheets(1)
Exceldoc.Select
You can remove “Selection =”, if you think you are not using it in code to check any condition.
Make sure these lines appear like this —> without ‘Exceldoc’ or ‘Excel.Application’ or ‘Excel.Range’
Excel.ActiveSheet.Range(“M2”).Select
With Excel.Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Subject: RE: New problem: Object variable not set. New info!
Thanks.I tried your way.
Although there is no errors, the Excel isn’t formatted (at all), it just ignores that.
My way, although there is an error (which goes away if you add sleep command), it actually formats the Excel to…
Here is the code with your lines in; no errors but doesn’t format the Excel:.
Sub Initialize
’ FormatMacro Macro
’ Macro recorded 22/02/2007 by 3M
’
’
Dim Excel As Variant
Dim Exceldoc As Variant
'Sleep 5
Set Excel = CreateObject("Excel.application")
If Excel Is Nothing Then
Msgbox "Error!!! Agent was unable to open Microsoft Excel."
Exit Sub
End If
'Set Excel = CreateObject("Excel.Application") 'Create Excel object
'Set Excel = GetObject(,"Excel.Application")
'Set Exceldoc = Excel.ActiveWorkbook 'Get a handle for the active document
Excel.Workbooks.Add
Set Exceldoc = Excel.Workbooks(1).Worksheets(1)
Exceldoc.Select
Const xlrange =2
Const xlColumns=2
'Const Columns=2
Const xlGeneral=1
Const xlBottom=-4107
Const xlHorizontal=-4128
Dim Range As Variant
Dim Columns As Variant
Columns = xlColumns
'Excel.Application.Columns("M:M").Select
' FormatMacro Macro
Subject: RE: New problem: Object variable not set. New info!
Wow. That’s strange. I tested it on then posted. Have no clue what is the problem. So are you sure that your code stopped working after you revert back to your code finally, even with sleep…? Is it not complaining on this line? Selection=Excel.Application.Range(“M3”).Select
Did you forget to comment it out as you are using the same, down below in your code as Excel.ActiveSheet.Range(“M3”).Select
Subject: RE: New problem: Object variable not set. New info!
Munish!
I have fixed it!
Thanks for your help and that of everyone else!
I decided that, the debugger meant it was running slowly (waiting for me to click something), so I added a Sleep statement…
Its very bizaare! I shouldnt need for him to sleep!
But maybe when he wakes up he notices an Excel thingie!
Sub Initialize
’ FormatMacro Macro
’ Macro recorded 22/02/2007 by 3M
’
’
Dim Excel As Variant
Dim Exceldoc As Variant
Sleep 5
Activateapp "Microsoft Excel"
'Set Excel = CreateObject("Excel.Application") 'Create Excel object
Set Excel = GetObject(,"Excel.Application")
Set Exceldoc = Excel.ActiveWorkbook 'Get a handle for the active document
Const xlrange =2
Const xlColumns=2
'Const Columns=2
Const xlGeneral=1
Const xlBottom=-4107
Const xlHorizontal=-4128
Dim Range As Variant
Dim Columns As Variant
Columns = xlColumns
'Excel.Application.Columns("M:M").Select
' FormatMacro Macro