Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Access Export Freeze Pane Code Error

Discussion in 'Information Technology' started by Gina, Jul 28, 2006.

  1. Gina

    Gina
    Expand Collapse
    Guest

    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

    --
    Gina
     
  2. Loading...


  3. Gina

    Gina
    Expand Collapse
    Guest

    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.
    --
    Gina


    "Gina" wrote:

    > 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
    >
    > --
    > Gina
     
  4. Ron2006

    Ron2006
    Expand Collapse
    Guest

    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
     
  5. Gina

    Gina
    Expand Collapse
    Guest

    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.
    --
    Gina


    "Ron2006" wrote:

    > 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
    >
    >
     
  6. Gina

    Gina
    Expand Collapse
    Guest

    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


    --
    Gina


    "Gina" wrote:

    > 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.
    > --
    > Gina
    >
    >
    > "Ron2006" wrote:
    >
    > > 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
    > >
    > >
     
  7. Ron2006

    Ron2006
    Expand Collapse
    Guest

    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
     
  8. Ron2006

    Ron2006
    Expand Collapse
    Guest

    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:
     
  9. RoyVidar

    RoyVidar
    Expand Collapse
    Guest

    Gina wrote in message
    <2F6F1D13-A16F-4200-A245-915B6B1129C3@microsoft.com> :
    > 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

    --
    Roy-Vidar
     

Share This Page