Format Export to Excel error

[B. Meincke]

I'm afraid I just don't understand why the following code runs without error
the first time, but returns an "Object variable or With block variable not
set" error on the split when run for a second time (the Excel file exists).

Can anyone shed some light on what I am doing wrong?

Thanks in advance.

***************************

Private Sub cmdExport_Click()

Dim objExcel As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet

Dim strFile As String
Dim strDest As String

Dim strMyDB As String

strMyDB = CurrentDb.Name
strPath = Left(strMyDB, LastInStr(strMyDB, "\"))
strFile = strPath & "This week's Open Houses.xls"

If Dir(strFile) <> "" Then
Kill strFile
End If

Sleep 3000

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryOHLReview", strFile, True

Set objExcel = New Excel.Application

objExcel.Visible = True

Set xlWB = objExcel.Workbooks.Open(strFile)

Set xlWS = xlWB.ActiveSheet

With xlWS

.Range("A1:R1").Font.Bold = True
.Range("A1:R1").HorizontalAlignment = xlCenter
.Range("A1:R1").Font.ColorIndex = 2
.Range("A1:R1").Interior.ColorIndex = 1

.Range("A:R").HorizontalAlignment = xlLeft
.Range("E:E").NumberFormat = "$#,##0.00"
.Range("E:E").HorizontalAlignment = xlRight

.Range("A:R").Columns.AutoFit

.Range("2:2").Select
End With

With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With

ActiveWindow.FreezePanes = True

xlWS.Range("A1:A1").Select

Set objExcel = Nothing
Set xlWB = Nothing
Set xlWS = Nothing

End Sub

********************************

 

[Maurice]

have you tried experimenting with different values for the splitcolumns and
splitrows. I mean splitcolumn = 0 maybe it doesn't exist?

 

[B. Meincke]

Thank you for your repy Maurice, but from what I understand "splitcolumn = 0"
just means that no columns are split. And I'm afraid I still wouldn't
understand why the code executes the first time when the file does not yet
exist but fails once it does. I have actually tried the code with the
splitcolumn line remmed out and it still fails at the FreezePanes line.

 

[Ken Snell MVP]

Many problems with the code.

You are creating new objects in your code (objExcel, xlWB, xlWS), but you do
not close them before setting them to Nothing. Also, you are setting them to
Nothing in the wrong order -- always close / set to Nothing the most nested
object first, then work your way back to the top level.

Also, you're using ActiveWorksheet and ActiveWindow, which create yet more
instances of EXCEL (other objects). All of this combines to cause the second
running of the code to find conflicts with leftover objects from the first
time. And, if you do not close the EXCEL application manually after the code
runs (your code leave the new EXCEL application running and open, along with
the workbook), you might run into problems.

Let's try this code:

Private Sub cmdExport_Click()

Dim objExcel As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet

Dim strFile As String
Dim strDest As String

Dim strMyDB As String

strMyDB = CurrentDb.Name
strPath = Left(strMyDB, LastInStr(strMyDB, "\"))
strFile = strPath & "This week's Open Houses.xls"

If Dir(strFile) <> "" Then
Kill strFile
End If

Sleep 3000

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryOHLReview", strFile, True

Set objExcel = New Excel.Application

objExcel.Visible = True

Set xlWB = objExcel.Workbooks.Open(strFile)

Set xlWS = xlWB.Worksheets(1)

With xlWS

.Range("A1:R1").Font.Bold = True
.Range("A1:R1").HorizontalAlignment = xlCenter
.Range("A1:R1").Font.ColorIndex = 2
.Range("A1:R1").Interior.ColorIndex = 1

.Range("A:R").HorizontalAlignment = xlLeft
.Range("E:E").NumberFormat = "$#,##0.00"
.Range("E:E").HorizontalAlignment = xlRight

.Range("A:R").Columns.AutoFit

.Range("2:2").Select
End With

With xlWS
.SplitColumn = 0
.SplitRow = 1
End With

xlWS.FreezePanes = True

xlWS.Range("A1:A1").Select

Set xlWS = Nothing
Set xlWB = Nothing
Set objExcel = Nothing

End Sub

 

[B. Meincke]

Thank you for your comprehensive reply, Ken. I appreciate my inexperience is
showing and I now recognize the shortcomings you mentioned. It's this sort of
thing that makes these forums invaluable to me.

I have tried the code you suggested which repairs some of my shortcomings,
of course, but it continues to return an error at the line:

.SplitColumn = 0

This time, however, the "Compile error: Method or data member not found" is
returned every time.

I have the MS Excel 12 Object Library installed in the database. Is there
another reference library that I may be missing that could be causing this
error to persist?

I should also add that this code is running under Access 2007.

Again, thank you for your reply. I look forward to any further suggestions
you might have to offer.

--
BJM
ACE Assistant
Gary Allan High School

Many problems with the code.

You are creating new objects in your code (objExcel, xlWB, xlWS), but you do
not close them before setting them to Nothing. Also, you are setting them to
Nothing in the wrong order -- always close / set to Nothing the most nested
object first, then work your way back to the top level.

Also, you're using ActiveWorksheet and ActiveWindow, which create yet more
instances of EXCEL (other objects). All of this combines to cause the second
running of the code to find conflicts with leftover objects from the first
time. And, if you do not close the EXCEL application manually after the code
runs (your code leave the new EXCEL application running and open, along with
the workbook), you might run into problems.

Let's try this code:

Private Sub cmdExport_Click()

Dim objExcel As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet

Dim strFile As String
Dim strDest As String

Dim strMyDB As String

strMyDB = CurrentDb.Name
strPath = Left(strMyDB, LastInStr(strMyDB, "\"))
strFile = strPath & "This week's Open Houses.xls"

If Dir(strFile) <> "" Then
Kill strFile
End If

Sleep 3000

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryOHLReview", strFile, True

Set objExcel = New Excel.Application

objExcel.Visible = True

Set xlWB = objExcel.Workbooks.Open(strFile)

Set xlWS = xlWB.Worksheets(1)

With xlWS

.Range("A1:R1").Font.Bold = True
.Range("A1:R1").HorizontalAlignment = xlCenter
.Range("A1:R1").Font.ColorIndex = 2
.Range("A1:R1").Interior.ColorIndex = 1

.Range("A:R").HorizontalAlignment = xlLeft
.Range("E:E").NumberFormat = "$#,##0.00"
.Range("E:E").HorizontalAlignment = xlRight

.Range("A:R").Columns.AutoFit

.Range("2:2").Select
End With

With xlWS
.SplitColumn = 0
.SplitRow = 1
End With

xlWS.FreezePanes = True

xlWS.Range("A1:A1").Select

Set xlWS = Nothing
Set xlWB = Nothing
Set objExcel = Nothing

End Sub

 

[Ken Snell MVP]

Ok, sorry, I substituted the wrong object for ActiveWindow. Try this:

Private Sub cmdExport_Click()

Dim objExcel As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet

Dim strFile As String
Dim strDest As String

Dim strMyDB As String

strMyDB = CurrentDb.Name
strPath = Left(strMyDB, LastInStr(strMyDB, "\"))
strFile = strPath & "This week's Open Houses.xls"

If Dir(strFile) <> "" Then
Kill strFile
End If

Sleep 3000

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryOHLReview", strFile, True

Set objExcel = New Excel.Application

objExcel.Visible = True

Set xlWB = objExcel.Workbooks.Open(strFile)

Set xlWS = xlWB.Worksheets(1)

With xlWS

.Range("A1:R1").Font.Bold = True
.Range("A1:R1").HorizontalAlignment = xlCenter
.Range("A1:R1").Font.ColorIndex = 2
.Range("A1:R1").Interior.ColorIndex = 1

.Range("A:R").HorizontalAlignment = xlLeft
.Range("E:E").NumberFormat = "$#,##0.00"
.Range("E:E").HorizontalAlignment = xlRight

.Range("A:R").Columns.AutoFit

.Range("2:2").Select
End With

With objExcel
.SplitColumn = 0
.SplitRow = 1
End With

xlWS.FreezePanes = True

xlWS.Range("A1:A1").Select

Set xlWS = Nothing
Set xlWB = Nothing
Set objExcel = Nothing

End Sub

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thank you for your comprehensive reply, Ken. I appreciate my inexperience
is
showing and I now recognize the shortcomings you mentioned. It's this sort
of
thing that makes these forums invaluable to me.

I have tried the code you suggested which repairs some of my shortcomings,
of course, but it continues to return an error at the line:

.SplitColumn = 0

This time, however, the "Compile error: Method or data member not found"
is
returned every time.

I have the MS Excel 12 Object Library installed in the database. Is there
another reference library that I may be missing that could be causing this
error to persist?

I should also add that this code is running under Access 2007.

Again, thank you for your reply. I look forward to any further suggestions
you might have to offer.

 

[B. Meincke]

Once again, Ken, I can't thank you enough for your continued patience, but,
unfortunately your revised code still fails at:
.SplitColumn = 0

I guess I really need to put some more study into the whole object model
thing. I think I could better understand when you were trying to split/freeze
an Excel worksheet object rather than an Excel object, which I take it refers
to the application itself.

I have tried replacing the ActiveWindow object at the troublesome point with
all three (xlWB, xlWS, and objExcel) to no avail in any case.

I suppose I could let freezing the first row go but as this worksheet is
likely to contain hundreds of records, it would sure be nice for users to
have the field headers always there.

Hope you can give this project one more look. Again, thanks.

 

[B. Meincke]

Respecting the fact that using the ActiveWindow object may not be the best
idea, after proving my mother's philosophy that I am stubborn to the point of
stupidity correct, I am pleased to report that the following works:

With objExcel.ActiveWindow
.SplitColumn = 0
.SplitRow = 1
.FreezePanes = True
End With

But the fact still remains that I could not have come to this conclusion
without the help of these newsgoups. Thanks you so much!

 

[Ken Snell MVP]

Glad you got it to work. I've never found a use/need for the ActiveWindow
object.