I'm getting an "Object Variable or With Variable not Set" error.  Here is 
my code with the offending line.

It's quite strange.... it seems the goes through.  Then when I execute it 
again it pops up with the error.  Then I execute the code again and the 
error is gone.  This error pops up every other time.

Private Sub bExport_Click()

DoCmd.TransferSpreadsheet acExport, 
acSpreadsheetTypeExcel8, "TruckSku_CrossTab", "C:\Test.xls", True

' Create a Microsoft Excel object.
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
' Open the spreadsheet to which you exported the data.
Set xlWrkbk = xlApp.Workbooks.Open("C:\Test.xls")

'Inserts a Row
xlApp.Worksheets(1).Rows(1).Insert

xlApp.Worksheets(1).Columns("A").ColumnWidth = 12
xlApp.Worksheets(1).Rows(1).RowHeight = 39.75
xlApp.Worksheets(1).Range("A1:E1").HorizontalAlignment = xlCenter
xlApp.Worksheets(1).Rows(2).Hidden = True
xlApp.Worksheets(1).Columns("B:I").ColumnWidth = 8
xlApp.Worksheets(1).PageSetup.PrintGridlines = True

xlApp.Worksheets(1).Range("A1").Select
xlApp.Worksheets(1).Range("A1", ActiveCell.End(xlToRight)).Font.Bold = 
True  'This is the bad line
xlApp.Worksheets(1).Range(ActiveCell, ActiveCell.End(xlToRight)).WrapText 
= True

Can someone help?
Thanks.

Bryan

Are you closing the object variables at the end of the procedure?

Set xlApp=Nothing
SET xlWrkbk =Nothing

Could be the problem...

Brian

>  from:    Bryan <kushwood@e...>
>  date:    Fri, 14 Mar 2003 09:15:31
>  to:      access@p...
>  subject: Re: [access] Object Variable or With Variable not Set
> 
> I'm getting an "Object Variable or With Variable not Set" error.  Here is 
> my code with the offending line.
> 
> It's quite strange.... it seems the goes through.  Then when I execute it 
> again it pops up with the error.  Then I execute the code again and the 
> error is gone.  This error pops up every other time.
> 
> Private Sub bExport_Click()
> 
> DoCmd.TransferSpreadsheet acExport, 
> acSpreadsheetTypeExcel8, "TruckSku_CrossTab", "C:\Test.xls", True
> 
> ' Create a Microsoft Excel object.
> Set xlApp = CreateObject("Excel.Application")
> xlApp.Visible = True
> ' Open the spreadsheet to which you exported the data.
> Set xlWrkbk = xlApp.Workbooks.Open("C:\Test.xls")
> 
> 'Inserts a Row
> xlApp.Worksheets(1).Rows(1).Insert
> 
> xlApp.Worksheets(1).Columns("A").ColumnWidth = 12
> xlApp.Worksheets(1).Rows(1).RowHeight = 39.75
> xlApp.Worksheets(1).Range("A1:E1").HorizontalAlignment = xlCenter
> xlApp.Worksheets(1).Rows(2).Hidden = True
> xlApp.Worksheets(1).Columns("B:I").ColumnWidth = 8
> xlApp.Worksheets(1).PageSetup.PrintGridlines = True
> 
> xlApp.Worksheets(1).Range("A1").Select
> xlApp.Worksheets(1).Range("A1", ActiveCell.End(xlToRight)).Font.Bold = 
> True  'This is the bad line
> xlApp.Worksheets(1).Range(ActiveCell, ActiveCell.End(xlToRight)).WrapText 
> = True
> 
> Can someone help?
> Thanks.

Depending on the requirements it may also be advisable before releasing 
the excel app variable to do the following

'Quit excel
xlapp.quit
'Then set variable as nothing
set xlapp = nothing

Hope this helps
Rob

> Depending on the requirements it may also be advisable before releasing 
t> he excel app variable to do the following

> 'Quit excel
x> lapp.quit
'> Then set variable as nothing
s> et xlapp = nothing

> Hope this helps
R> ob

Thanks for the reply...
This was a great idea but it won't work... besides I want the user to view 
the spreadsheet before they close it...  Have any other suggestions?  It's 
crazy but this problem seems to be a Micorsoft problem... I've worked on 
it for the last 2 days.  I have all the updated pathes for Office and 
still nothing.

Brian,

Thanks for the reply...
This was a great idea but it won't work...  Have any other suggestions?  I 
think this is a Microsoft problem.  I have read many websites that have 
suggested to do what I'm doing but yet there is major problem with this 
Range deal and bordering.  This is where I'm getting it.

> Bryan

Are you closing the object variables at the end of the procedure?

Set xlApp=Nothing
SET xlWrkbk =Nothing

Could be the problem...

Brian

>  from:    Bryan <kushwood@e...>
>  date:    Fri, 14 Mar 2003 09:15:31
>  to:      access@p...
>  subject: Re: [access] Object Variable or With Variable not Set
> 
> I'm getting an "Object Variable or With Variable not Set" error.  Here 
is 
> my code with the offending line.
> 
> It's quite strange.... it seems the goes through.  Then when I execute 
it 
> again it pops up with the error.  Then I execute the code again and the 
> error is gone.  This error pops up every other time.
> 
> Private Sub bExport_Click()
> 
> DoCmd.TransferSpreadsheet acExport, 
> acSpreadsheetTypeExcel8, "TruckSku_CrossTab", "C:\Test.xls", True
> 
> ' Create a Microsoft Excel object.
> Set xlApp = CreateObject("Excel.Application")
> xlApp.Visible = True
> ' Open the spreadsheet to which you exported the data.
> Set xlWrkbk = xlApp.Workbooks.Open("C:\Test.xls")
> 
> 'Inserts a Row
> xlApp.Worksheets(1).Rows(1).Insert
> 
> xlApp.Worksheets(1).Columns("A").ColumnWidth = 12
> xlApp.Worksheets(1).Rows(1).RowHeight = 39.75
> xlApp.Worksheets(1).Range("A1:E1").HorizontalAlignment = xlCenter
> xlApp.Worksheets(1).Rows(2).Hidden = True
> xlApp.Worksheets(1).Columns("B:I").ColumnWidth = 8
> xlApp.Worksheets(1).PageSetup.PrintGridlines = True
> 
> xlApp.Worksheets(1).Range("A1").Select
> xlApp.Worksheets(1).Range("A1", ActiveCell.End(xlToRight)).Font.Bold = 
> True  'This is the bad line
> xlApp.Worksheets(1).Range(ActiveCell, ActiveCell.End
(xlToRight)).WrapText 
> = True
> 
> Can someone help?
> Thanks.

Brian,

Thanks for the reply...
This was a great idea but it won't work...  Have any other suggestions?  I 
think this is a Microsoft problem.  I have read many websites that have 
suggested to do what I'm doing but yet there is major problem with this 
Range deal and bordering.

> Bryan

Are you closing the object variables at the end of the procedure?

Set xlApp=Nothing
SET xlWrkbk =Nothing

Could be the problem...

Brian

>  from:    Bryan <kushwood@e...>
>  date:    Fri, 14 Mar 2003 09:15:31
>  to:      access@p...
>  subject: Re: [access] Object Variable or With Variable not Set
> 
> I'm getting an "Object Variable or With Variable not Set" error.  Here 
is 
> my code with the offending line.
> 
> It's quite strange.... it seems the goes through.  Then when I execute 
it 
> again it pops up with the error.  Then I execute the code again and the 
> error is gone.  This error pops up every other time.
> 
> Private Sub bExport_Click()
> 
> DoCmd.TransferSpreadsheet acExport, 
> acSpreadsheetTypeExcel8, "TruckSku_CrossTab", "C:\Test.xls", True
> 
> ' Create a Microsoft Excel object.
> Set xlApp = CreateObject("Excel.Application")
> xlApp.Visible = True
> ' Open the spreadsheet to which you exported the data.
> Set xlWrkbk = xlApp.Workbooks.Open("C:\Test.xls")
> 
> 'Inserts a Row
> xlApp.Worksheets(1).Rows(1).Insert
> 
> xlApp.Worksheets(1).Columns("A").ColumnWidth = 12
> xlApp.Worksheets(1).Rows(1).RowHeight = 39.75
> xlApp.Worksheets(1).Range("A1:E1").HorizontalAlignment = xlCenter
> xlApp.Worksheets(1).Rows(2).Hidden = True
> xlApp.Worksheets(1).Columns("B:I").ColumnWidth = 8
> xlApp.Worksheets(1).PageSetup.PrintGridlines = True
> 
> xlApp.Worksheets(1).Range("A1").Select
> xlApp.Worksheets(1).Range("A1", ActiveCell.End(xlToRight)).Font.Bold = 
> True  'This is the bad line
> xlApp.Worksheets(1).Range(ActiveCell, ActiveCell.End
(xlToRight)).WrapText 
> = True
> 
> Can someone help?
> Thanks.

What specific line of code is giving you the error?

John V. Ruff - The Eternal Optimist :-)
Always Looking For Contract Opportunities

www.noclassroom.com
Live software training
Right over the Internet

Home:  xxx.xxx.xxxx
Cell: 253.307/2947
9306 Farwest Dr SW
Lakewood, WA 98498

"Commit to the Lord whatever you do,
	and your plans will succeed." Proverbs 16:3



-----Original Message-----
From: Bryan [mailto:kushwood@e...] 
Sent: Friday, March 14, 2003 9:16 AM
To: Access
Subject: [access] Object Variable or With Variable not Set


I'm getting an "Object Variable or With Variable not Set" error.  Here is 
my code with the offending line.

It's quite strange.... it seems the goes through.  Then when I execute it 
again it pops up with the error.  Then I execute the code again and the 
error is gone.  This error pops up every other time.

Private Sub bExport_Click()

DoCmd.TransferSpreadsheet acExport, 
acSpreadsheetTypeExcel8, "TruckSku_CrossTab", "C:\Test.xls", True

' Create a Microsoft Excel object.
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
' Open the spreadsheet to which you exported the data.
Set xlWrkbk = xlApp.Workbooks.Open("C:\Test.xls")

'Inserts a Row
xlApp.Worksheets(1).Rows(1).Insert

xlApp.Worksheets(1).Columns("A").ColumnWidth = 12
xlApp.Worksheets(1).Rows(1).RowHeight = 39.75
xlApp.Worksheets(1).Range("A1:E1").HorizontalAlignment = xlCenter
xlApp.Worksheets(1).Rows(2).Hidden = True
xlApp.Worksheets(1).Columns("B:I").ColumnWidth = 8
xlApp.Worksheets(1).PageSetup.PrintGridlines = True

xlApp.Worksheets(1).Range("A1").Select
xlApp.Worksheets(1).Range("A1", ActiveCell.End(xlToRight)).Font.Bold = 
True  'This is the bad line xlApp.Worksheets(1).Range(ActiveCell,
ActiveCell.End(xlToRight)).WrapText 
= True

Can someone help?
Thanks.

I just wanted to mention... this problem doesn't come up when I close 
Access and reneter Access.  It seems like access is holding on to 
something after running the following script.


> I'm getting an "Object Variable or With Variable not Set" error.  Here 
is 
m> y code with the offending line.

> It's quite strange.... it seems the goes through.  Then when I execute 
it 
a> gain it pops up with the error.  Then I execute the code again and the 
e> rror is gone.  This error pops up every other time.

> Private Sub bExport_Click()

> DoCmd.TransferSpreadsheet acExport, 
a> cSpreadsheetTypeExcel8, "TruckSku_CrossTab", "C:\Test.xls", True

> ' Create a Microsoft Excel object.
S> et xlApp = CreateObject("Excel.Application")
x> lApp.Visible = True
'>  Open the spreadsheet to which you exported the data.
S> et xlWrkbk = xlApp.Workbooks.Open("C:\Test.xls")

> 'Inserts a Row
x> lApp.Worksheets(1).Rows(1).Insert

> xlApp.Worksheets(1).Columns("A").ColumnWidth = 12
x> lApp.Worksheets(1).Rows(1).RowHeight = 39.75
x> lApp.Worksheets(1).Range("A1:E1").HorizontalAlignment = xlCenter
x> lApp.Worksheets(1).Rows(2).Hidden = True
x> lApp.Worksheets(1).Columns("B:I").ColumnWidth = 8
x> lApp.Worksheets(1).PageSetup.PrintGridlines = True

> xlApp.Worksheets(1).Range("A1").Select
x> lApp.Worksheets(1).Range("A1", ActiveCell.End(xlToRight)).Font.Bold = 
T> rue  'This is the bad line
x> lApp.Worksheets(1).Range(ActiveCell, ActiveCell.End
(xlToRight)).WrapText 
=>  True

> Can someone help?
T> hanks.

Bryan,

Do you have "Option Explicit" in the Declarations Section of the module?

If so, then you must declare your variables like this:

Private Sub bExport_Click()

Dim xlApp as object
Dim xlWrkbk as object

...the rest of your code

John V. Ruff - The Eternal Optimist :-)
Always Looking For Contract Opportunities

www.noclassroom.com
Live software training
Right over the Internet

Home:  xxx.xxx.xxxx
Cell: 253.307/2947
9306 Farwest Dr SW
Lakewood, WA 98498

"Commit to the Lord whatever you do,
	and your plans will succeed." Proverbs 16:3



-----Original Message-----
From: Bryan [mailto:kushwood@e...] 
Sent: Friday, March 14, 2003 9:16 AM
To: Access
Subject: [access] Object Variable or With Variable not Set


I'm getting an "Object Variable or With Variable not Set" error.  Here is 
my code with the offending line.

It's quite strange.... it seems the goes through.  Then when I execute it 
again it pops up with the error.  Then I execute the code again and the 
error is gone.  This error pops up every other time.

Private Sub bExport_Click()

DoCmd.TransferSpreadsheet acExport, 
acSpreadsheetTypeExcel8, "TruckSku_CrossTab", "C:\Test.xls", True

' Create a Microsoft Excel object.
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
' Open the spreadsheet to which you exported the data.
Set xlWrkbk = xlApp.Workbooks.Open("C:\Test.xls")

'Inserts a Row
xlApp.Worksheets(1).Rows(1).Insert

xlApp.Worksheets(1).Columns("A").ColumnWidth = 12
xlApp.Worksheets(1).Rows(1).RowHeight = 39.75
xlApp.Worksheets(1).Range("A1:E1").HorizontalAlignment = xlCenter
xlApp.Worksheets(1).Rows(2).Hidden = True
xlApp.Worksheets(1).Columns("B:I").ColumnWidth = 8
xlApp.Worksheets(1).PageSetup.PrintGridlines = True

xlApp.Worksheets(1).Range("A1").Select
xlApp.Worksheets(1).Range("A1", ActiveCell.End(xlToRight)).Font.Bold = 
True  'This is the bad line xlApp.Worksheets(1).Range(ActiveCell,
ActiveCell.End(xlToRight)).WrapText 
= True

Can someone help?
Thanks.

This is the bad line of code and I've declared my variable in the 
declarations area.

xlApp.Worksheets(1).Range("A1", ActiveCell.End(xlToRight)).Font.Bold = 
True  'This is the bad 

> What specific line of code is giving you the error?

John V. Ruff - The Eternal Optimist :-)
Always Looking For Contract Opportunities

www.noclassroom.com
Live software training
Right over the Internet

Home:  xxx.xxx.xxxx
Cell: 253.307/2947
9306 Farwest Dr SW
Lakewood, WA 98498

"Commit to the Lord whatever you do,
	and your plans will succeed." Proverbs 16:3



-----Original Message-----
From: Bryan [mailto:kushwood@e...] 
Sent: Friday, March 14, 2003 9:16 AM
To: Access
Subject: [access] Object Variable or With Variable not Set


I'm getting an "Object Variable or With Variable not Set" error.  Here is 
my code with the offending line.

It's quite strange.... it seems the goes through.  Then when I execute it 
again it pops up with the error.  Then I execute the code again and the 
error is gone.  This error pops up every other time.

Private Sub bExport_Click()

DoCmd.TransferSpreadsheet acExport, 
acSpreadsheetTypeExcel8, "TruckSku_CrossTab", "C:\Test.xls", True

' Create a Microsoft Excel object.
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
' Open the spreadsheet to which you exported the data.
Set xlWrkbk = xlApp.Workbooks.Open("C:\Test.xls")

'Inserts a Row
xlApp.Worksheets(1).Rows(1).Insert

xlApp.Worksheets(1).Columns("A").ColumnWidth = 12
xlApp.Worksheets(1).Rows(1).RowHeight = 39.75
xlApp.Worksheets(1).Range("A1:E1").HorizontalAlignment = xlCenter
xlApp.Worksheets(1).Rows(2).Hidden = True
xlApp.Worksheets(1).Columns("B:I").ColumnWidth = 8
xlApp.Worksheets(1).PageSetup.PrintGridlines = True

xlApp.Worksheets(1).Range("A1").Select
xlApp.Worksheets(1).Range("A1", ActiveCell.End(xlToRight)).Font.Bold = 
True  'This is the bad line xlApp.Worksheets(1).Range(ActiveCell,
ActiveCell.End(xlToRight)).WrapText 
= True

Can someone help?
Thanks.

Bryan

I had this problem, and did exactly what you are doing. I scoured the
internet, posted messages on boards, asked every VB expert I know and drew a
blank on all counts.

Like you, the conclusion i came to was that it is a microsoft issue - the
code was definately 100% ok.
In the end, we abandoned the problem line and worked around it a different
way.

Sorry i couldn't help!

Sarah



-----Original Message-----
From: Bryan [mailto:kushwood@e...]
Sent: 14 March 2003 17:00
To: Access
Subject: [access] Re: Object Variable or With Variable not Set


Brian,

Thanks for the reply...
This was a great idea but it won't work...  Have any other suggestions?  I 
think this is a Microsoft problem.  I have read many websites that have 
suggested to do what I'm doing but yet there is major problem with this 
Range deal and bordering.



_________________________________________________________
This email is confidential and intended solely for the use of the 
individual to whom it is addressed. Any views or opinions presented are 
solely those of the author and do not necessarily represent those of 
SchlumbergerSema.
If you are not the intended recipient, be advised that you have received
this email in error and that any use, dissemination, forwarding, printing, 
or copying of this email is strictly prohibited.

If you have received this email in error please notify the
SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600.
_________________________________________________________

Thanks, Sarah

At least I know it's not me... I'll have to drop Microsoft a line and tell 
them about the problem.


> Bryan

I had this problem, and did exactly what you are doing. I scoured the
internet, posted messages on boards, asked every VB expert I know and drew 
a
blank on all counts.

Like you, the conclusion i came to was that it is a microsoft issue - the
code was definately 100% ok.
In the end, we abandoned the problem line and worked around it a different
way.

Sorry i couldn't help!

Sarah



-----Original Message-----
From: Bryan [mailto:kushwood@e...]
Sent: 14 March 2003 17:00
To: Access
Subject: [access] Re: Object Variable or With Variable not Set


Brian,

Thanks for the reply...
This was a great idea but it won't work...  Have any other suggestions?  I 
think this is a Microsoft problem.  I have read many websites that have 
suggested to do what I'm doing but yet there is major problem with this 
Range deal and bordering.



_________________________________________________________
This email is confidential and intended solely for the use of the 
individual to whom it is addressed. Any views or opinions presented are 
solely those of the author and do not necessarily represent those of 
SchlumbergerSema.
If you are not the intended recipient, be advised that you have received
this email in error and that any use, dissemination, forwarding, printing, 
or copying of this email is strictly prohibited.

If you have received this email in error please notify the
SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600.
_________________________________________________________

Your code is telling me that you want the last cell in Row A "Bold" and 
that
the last cell is to permit word wrapping.

I opened Excel, cut and pasted your code into a module I called Macro2 
and
then tried to run the macro.  I got errors, so I experimented until I 
got
the code to run correctly. Here it is:


    With Worksheets(1).Range("A1").End(xlToRight)
        .Font.Bold =3D True
        .WrapText =3D True
    End With

Whenever I use automation to work with Excel or Word, I often go to 
Tools >
Macro > Record New Macro and then fill the spreadsheet/Word document 
with
sample info, including formatting.  I then stop the macro and go to edit 
it.
The VBA code is there for me to examine and cut and paste to my Access
module.  Some modifications usually need to be made to the Access VBA 
code
to streamline it, but seeing the code that Excel/Word creates really 
helps
in automation.


John V. Ruff - The Eternal Optimist :-)
Always Looking For Contract Opportunities

www.noclassroom.com
Live software training
Right over the Internet

Home:  xxx.xxx.xxxx
Cell: 253.307/2947
9306 Farwest Dr SW
Lakewood, WA 98498

"Commit to the Lord whatever you do,
	and your plans will succeed." Proverbs 16:3



-----Original Message-----
From: Bryan [mailto:kushwood@e...]
Sent: Friday, March 14, 2003 5:11 PM
To: Access
Subject: [access] RE: Object Variable or With Variable not Set



This is the bad line of code and I've declared my variable in the
declarations area.

xlApp.Worksheets(1).Range("A1", ActiveCell.End(xlToRight)).Font.Bold =3D 

True  'This is the bad

> What specific line of code is giving you the error?

John V. Ruff - The Eternal Optimist :-)
Always Looking For Contract Opportunities

www.noclassroom.com
Live software training
Right over the Internet

Home:  xxx.xxx.xxxx
Cell: 253.307/2947
9306 Farwest Dr SW
Lakewood, WA 98498

"Commit to the Lord whatever you do,
	and your plans will succeed." Proverbs 16:3



-----Original Message-----
From: Bryan [mailto:kushwood@e...]
Sent: Friday, March 14, 2003 9:16 AM
To: Access
Subject: [access] Object Variable or With Variable not Set


I'm getting an "Object Variable or With Variable not Set" error.  Here 
is
my code with the offending line.

It's quite strange.... it seems the goes through.  Then when I execute 
it
again it pops up with the error.  Then I execute the code again and the
error is gone.  This error pops up every other time.

Private Sub bExport_Click()

DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel8, "TruckSku_CrossTab", "C:\Test.xls", True

' Create a Microsoft Excel object.
Set xlApp =3D CreateObject("Excel.Application")
xlApp.Visible =3D True
' Open the spreadsheet to which you exported the data.
Set xlWrkbk =3D xlApp.Workbooks.Open("C:\Test.xls")

'Inserts a Row
xlApp.Worksheets(1).Rows(1).Insert

xlApp.Worksheets(1).Columns("A").ColumnWidth =3D 12
xlApp.Worksheets(1).Rows(1).RowHeight =3D 39.75
xlApp.Worksheets(1).Range("A1:E1").HorizontalAlignment =3D xlCenter
xlApp.Worksheets(1).Rows(2).Hidden =3D True
xlApp.Worksheets(1).Columns("B:I").ColumnWidth =3D 8
xlApp.Worksheets(1).PageSetup.PrintGridlines =3D True

xlApp.Worksheets(1).Range("A1").Select
xlApp.Worksheets(1).Range("A1", ActiveCell.End(xlToRight)).Font.Bold =3D 

True  'This is the bad line xlApp.Worksheets(1).Range(ActiveCell,
ActiveCell.End(xlToRight)).WrapText
=3D True

Can someone help?
Thanks.

Oops, I forgot the xlApp object.  Also, to make your code more readable and
speed its process (even if just a fraction of a milli-second) use With and
End With.


	'Inserts a Row
	With xlApp.Worksheets(1)
		.Rows(1).Insert
		.Columns("A").ColumnWidth = 12 
		.Rows(1).RowHeight = 39.75 
		.Range("A1:E1").HorizontalAlignment = xlCenter 
		.Rows(2).Hidden = True 
		.Columns("B:I").ColumnWidth = 8
xlApp.Worksheets(1).PageSetup.PrintGridlines = True
	End With

    	With xlApp.Worksheets(1).Range("A1").End(xlToRight)
        	.Font.Bold = True
        	.WrapText = True
    	End With

John V. Ruff - The Eternal Optimist :-)
Always Looking For Contract Opportunities

www.noclassroom.com
Live software training
Right over the Internet

Home:  xxx.xxx.xxxx
Cell: 253.307/2947
9306 Farwest Dr SW
Lakewood, WA 98498

"Commit to the Lord whatever you do,
	and your plans will succeed." Proverbs 16:3



-----Original Message-----
From: Bryan [mailto:kushwood@e...] 
Sent: Friday, March 14, 2003 5:00 PM
To: Access
Subject: [access] Re: Object Variable or With Variable not Set


Brian,

Thanks for the reply...
This was a great idea but it won't work...  Have any other suggestions?  I 
think this is a Microsoft problem.  I have read many websites that have 
suggested to do what I'm doing but yet there is major problem with this 
Range deal and bordering.

> Bryan

Are you closing the object variables at the end of the procedure?

Set xlApp=Nothing
SET xlWrkbk =Nothing

Could be the problem...

Brian

>  from:    Bryan <kushwood@e...>
>  date:    Fri, 14 Mar 2003 09:15:31
>  to:      access@p...
>  subject: Re: [access] Object Variable or With Variable not Set
> 
> I'm getting an "Object Variable or With Variable not Set" error.  Here
is 
> my code with the offending line.
> 
> It's quite strange.... it seems the goes through.  Then when I execute
it 
> again it pops up with the error.  Then I execute the code again and 
> the
> error is gone.  This error pops up every other time.
> 
> Private Sub bExport_Click()
> 
> DoCmd.TransferSpreadsheet acExport,
> acSpreadsheetTypeExcel8, "TruckSku_CrossTab", "C:\Test.xls", True
> 
> ' Create a Microsoft Excel object.
> Set xlApp = CreateObject("Excel.Application")
> xlApp.Visible = True
> ' Open the spreadsheet to which you exported the data.
> Set xlWrkbk = xlApp.Workbooks.Open("C:\Test.xls")
> 
> 'Inserts a Row
> xlApp.Worksheets(1).Rows(1).Insert
> 
> xlApp.Worksheets(1).Columns("A").ColumnWidth = 12 
> xlApp.Worksheets(1).Rows(1).RowHeight = 39.75 
> xlApp.Worksheets(1).Range("A1:E1").HorizontalAlignment = xlCenter 
> xlApp.Worksheets(1).Rows(2).Hidden = True 
> xlApp.Worksheets(1).Columns("B:I").ColumnWidth = 8 
> xlApp.Worksheets(1).PageSetup.PrintGridlines = True
> 
> xlApp.Worksheets(1).Range("A1").Select
> xlApp.Worksheets(1).Range("A1", ActiveCell.End(xlToRight)).Font.Bold 
> True  'This is the bad line
> xlApp.Worksheets(1).Range(ActiveCell, ActiveCell.End
(xlToRight)).WrapText 
> = True
> 
> Can someone help?
> Thanks.