Results 1 to 7 of 7

Thread: Help with Error 1004

  1. #1

    Thread Starter
    New Member
    Join Date
    Mar 2018
    Posts
    10

    Help with Error 1004

    I created a program in C# that generates an Excel File, the file also has the a VBA code that runs when the file is opened.
    When I open the file,the VBA code runs normal but if I close without saving it and and open it again I get Error 1004.
    After closing the error message I ran the code manually and it worked.
    The error happens in the line:

    "pf.CurrentPage = "Jan/2018""

    Code :
    Code:
    Private Sub Workbook_Open()
        For Each St In ActiveWorkbook.Worksheets
            For Each pt In St.PivotTables
                If InStr(pt.Name, "Hist") > 0 Then
                    pt.PivotFields(pt.RowFields(1).Name).AutoSort xlDescending, pt.DataFields(1).Name, pt.PivotColumnAxis.PivotLines(12)
                End If
                For Each pf In pt.PageFields
                    If pf.Name = "MÊS" Then
                        pf.CurrentPage = "Jan/2018"
                    End If
                Next
            Next
        Next
    End Sub

  2. #2
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Help with Error 1004

    i would try, moving the code to a separate procedure, then calling from workbook open with a small delay, using application.ontime
    code in workbook open often fails because the data has not yet loaded
    easy to try anyway
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    New Member
    Join Date
    Mar 2018
    Posts
    10

    Re: Help with Error 1004

    Is there a way to wait for the workbook for the data to load?

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Help with Error 1004

    Is there a way to wait for the workbook for the data to load?
    as suggested, use application.ontime, a 1 second delay should be enough
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6

    Thread Starter
    New Member
    Join Date
    Mar 2018
    Posts
    10

    Re: Help with Error 1004

    I used "ActiveWorkbook.RefreshAll" and it worked, is "application.ontime" better?

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Help with Error 1004

    is "application.ontime" better?
    for some problems, not others
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width