Access Export Freeze Pane Code Error

G

Gina

This is from Charles (Co-Worker)

I am creating an instance of excel after exporting a report from Access and
formatting it. The code runs through fine with the first report but errors
out on the next report. The problem is with the line



Rows("2:2").Select

With ActiveWorkbook

xlApp.ActiveWindow.FreezePanes = True

End With



I get an “Object variable or with block variable not set error.â€







Excel:

Set xlApp = CreateObject("Excel.Application") 'Create an instance of Excel

xlApp.Visible = False ' Make it invisible to the user



With xlApp



Workbooks.Open FileName:=strFileName, ReadOnly:=False 'Open the workbook
previously exported from Access

Rows("1:1").Select ' Select the header row and format

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

.Font.Bold = True

End With



Rows("2:2").Select

With ActiveWorkbook

xlApp.ActiveWindow.FreezePanes = True ' Freeze the row for scrolling

End With



Columns("D:D").Select

Selection.NumberFormat = "m/d/yy;@" ' Format cloumn as date

Columns("E:E").Select

Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($*
""-""??_);_(@_)" ' Format the column as Accounting with $ sign

Cells.Select

Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5), _

Replace:=True, PageBreaks:=False, SummaryBelowData:=True 'Sub total
the entire worksheet by the change in customer number

Cells.EntireColumn.AutoFit ' Auto the entire sheet

Range("A1").Select

ActiveWorkbook.Save 'Save the workbook

End With

xlApp.Quit 'Close Excel

Set xlApp = Nothing 'Release the instance of Excel
 
G

Gina

Additional information...

When the report is run the first time it works fine. However, when it is
run the second time 9with the expectation of it running the same as the first
time) it gets hung up on the described code below.
 
R

Ron2006

On the second time are you going into the same xls file.

If yes, have you tried to select the worksheet and turn freeze panes
OFF as the first thing you have the application do.


Ron
 
G

Gina

No not the same file, each time it is run, the code creates a new unique name
and then exports the data out to excel under this new name and then opens the
file and then formats it.
 
G

Gina

Here is the complete code, hopes this helps.


Private Sub AssignItems()

On Error GoTo err_Handler

'"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""'

'Once the user has determine all of the items they want to assign they must
be assigned to a person '

'"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""'



strName = Me.cmbEmpName.Value

If strName = Null Then 'Check to ensure that user has selected a person to
assign the items to.

fAssistant

Set MyBalloon = MyAssistant.NewBalloon

MyAssistant.Animation = msoAnimationThinking

With MyBalloon

.Heading = "No Employee Name Selected..."

.Text = "You must select a person to assign this open item to before
you will be allowed to continue" 'If no one has been selected notify the user
that they most select someone.

.Icon = msoIconAlertInfo

.Button = msoButtonSetOK

End With

MyBalloon.Animation = msoAnimationGetAttentionMajor

lReturn = MyBalloon.Show

If lReturn = -1 Then

Me.cmbEmpName.SetFocus 'Setfocus to the employee name drop down menu.

Exit Sub

End If



Else



strTable = strTableName ' determine which group to work with
"Biling, Collections, PrePass + etc."



intCount = 1

For i = 0 To lstAssigned.ListCount - 1 'Determine how many items have been
selected in the listbox that will be looped through

varCustID = lstAssigned.Column(0, intCount) 'Assign a the customer's ID
Number a variable

Set db = CurrentDb

Set rst = db.OpenRecordset(strTable, dbOpenDynaset)



With rst

.MoveFirst

.FindFirst "[ID] = " & varCustID & "" 'Locate a Customer Number that
matches the one selected on the listbox

.Edit

.Fields("AssignedTo") = strName 'Assign a person to the record

.Fields("DateAssigned") = Format(Date, "mm/dd/yyyy") 'Assign the
current date to the record

.Update

End With

rst.Close

intCount = intCount + 1 'Update the counter

If intCount = lstAssigned.ListCount Then 'Determine that we have come to
the end of the list

GoTo Finished

End If

Next i



Finished:

Set db = Nothing

Set rst = Nothing

End If


'""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""'

'Now that the records have been assigned print out a report for the person
to work the open items '


'""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""'

Select Case strTableName



Case "Billing"

stReport = "rptOutPutReport"

Case "Collections"

stReport = "rptCollectionReport"

Case "PPPLUS"

stReport = "rptPPPlus"

Case "Rollups"

stReport = "rptRollups"

Case "tbl999"

stReport = "rptStatus999"

End Select





stDocName = strName & "_" & Now() & ".xls"

strFileName = "F:\Billing and Collections\Worksheet
Maintenance\Reports\Assigned\" & stDocName

DoCmd.OutputTo acOutputReport, stReport, acFormatXLS, strFileName,
False



Excel:

Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True

xlApp.UserControl = True



With xlApp



Workbooks.Open FileName:=strFileName, ReadOnly:=False

Rows("1:1").Select

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

Selection.Font.Bold = True

'Rows("2:2").Select

'ActiveWindow.FreezePanes = True

Columns("D:D").Select

Selection.NumberFormat = "m/d/yy;@"

Columns("E:E").Select

Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($*
""-""??_);_(@_)"

Cells.Select

Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5), _

Replace:=True, PageBreaks:=False, SummaryBelowData:=True

Cells.EntireColumn.AutoFit

Range("A1").Select

Workbooks(strFileName).Close SaveChanges:=True

End With

xlApp.Quit

Set xlApp = Nothing



strTable = "EmpNames"

Set db = CurrentDb()

Set rst = db.OpenRecordset(strTable, dbOpenDynaset)

Criteria = Nz(cmbEmpName.Value, "")





With rst

.MoveFirst

.FindFirst "[Name] Like '" & Criteria & "'"

If rst.NoMatch Then

MsgBox "I was unable to locate an email address for " & Criteria
& _

vbCr & "I will not be able to email " & Criteria & " a
Maintenance Worhseet " & _

vbCr & "until this data has been updated", vbExclamation,
"No Email Address Found. "

DoCmd.Hourglass False

Exit Sub

End If

strRecip = rst.Fields("Email").Value

End With

rst.Close

Set rst = Nothing

Set db = Nothing



StrAttachment = strFileName

fAutoEmail

DoCmd.Hourglass False

frm_Requery



fAssistant

Set MyBalloon = MyAssistant.NewBalloon

MyAssistant.Animation = msoAnimationThinking

With MyBalloon

.Heading = "Assignment Complete...."

.Text = "The selected items have been assigned to " & strName
'Notify the user that the selected items have been assigned

.Icon = msoIconAlertInfo

.Button = msoButtonSetOK

End With

MyBalloon.Animation = msoAnimationGetAttentionMajor

lReturn = MyBalloon.Show

If lReturn = -1 Then

Me.cmbEmpName.SetFocus

End If



' MsgBox "The selected items have been assigned to " & strName





Exit Sub







err_Handler:



If Err.Number = 2501 Then

Exit Sub

End If



MsgBox Err.Number & " " & Err.Description & vbCrLf & "If this problem
persists, please contact the database administrator", vbCritical, "Error!"

rst.Close

Set rst = Nothing

Set db = Nothing

Exit Sub

End Sub
 
R

RoyVidar

Gina wrote in message
This is from Charles (Co-Worker)

I am creating an instance of excel after exporting a report from
Access and formatting it. The code runs through fine with the first
report but errors out on the next report. The problem is with the
line



Rows("2:2").Select

With ActiveWorkbook

xlApp.ActiveWindow.FreezePanes = True

End With



I get an “Object variable or with block variable not set error.â€







Excel:

Set xlApp = CreateObject("Excel.Application") 'Create an instance of
Excel

xlApp.Visible = False ' Make it invisible to the user



With xlApp



Workbooks.Open FileName:=strFileName, ReadOnly:=False 'Open the
workbook previously exported from Access

Rows("1:1").Select ' Select the header row and format

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

.Font.Bold = True

End With



Rows("2:2").Select

With ActiveWorkbook

xlApp.ActiveWindow.FreezePanes = True ' Freeze the row for
scrolling

End With



Columns("D:D").Select

Selection.NumberFormat = "m/d/yy;@" ' Format cloumn as date

Columns("E:E").Select

Selection.NumberFormat = "_($* #,##0.00_);_($*
(#,##0.00);_($* ""-""??_);_(@_)" ' Format the column as Accounting
with $ sign

Cells.Select

Selection.Subtotal GroupBy:=1, Function:=xlSum,
TotalList:=Array(5), _

Replace:=True, PageBreaks:=False, SummaryBelowData:=True 'Sub
total the entire worksheet by the change in customer number

Cells.EntireColumn.AutoFit ' Auto the entire sheet

Range("A1").Select

ActiveWorkbook.Save 'Save the workbook

End With

xlApp.Quit 'Close Excel

Set xlApp = Nothing 'Release the instance of Excel

The problem here, is probably that you are using excel properties,
methods and objects without qualifying them. Every Rows, Cells, Range,
Selection, Columns, Acitive<thingies> ... must be qualified through the
correct Excel object. I'm a bit of a fan of declaring and instantiating
objects both for workbook and worksheet, and refer through them.

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False ' Make it invisible to the user
With xlApp
set wr = .Workbooks.Open(FileName:=strFileName, ReadOnly:=False)
set sh = wr.sheets(1)
sh.Rows("1:1").Select ' Select the header row and format

With xl.Selection

....

I'm not 100 percent sure which object should qualify what, but you'll
probably get some help through Intellisence, and here's a little read
http://support.microsoft.com/default.aspx?kbid=178510
 
R

Ron2006

Instead of grabbing the rows and trying the freezepain (this did not
work when I tried it in excel on my computer) go to cell A:3
(column A row 3) and issue freeze pain then.


Ron
 
R

Ron2006

just looked at the code again and it should be cell A:2


The row 2:2 was commented out and I didn't see the comment mark.

You want to be in column A and in the cell just below the row you want
to be frozen.

Ron

Gina wrote:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top