Results 1 to 5 of 5

Thread: Run-time error '1004' HELP

  1. #1

    Thread Starter
    New Member
    Join Date
    Dec 2017
    Posts
    3

    Run-time error '1004' HELP

    Hi All,

    New to this forum as an infrequent, yet learning VBA coder. I am trying to write a script that loops through two lists, creating pdfs after each iteration.

    I had gotten it to work, but it only outputted the last excel sheet after running through list 2. I figured adding to the Filename portion would output for each run through list 1 and list 2. Instead of my initial pdf count of 72, I should have somewhere over 600. After trying to fix my script, I got a run-time 1004 error.

    Any insight into the below would be greatly appreciated.

    Sub PDF()
    Dim cell As Range
    Dim cell_2 As Range
    Dim wsSummary As Worksheet

    Set wsSummary = Sheets("CAE Summary_2")
    For Each cell In Worksheets("CAE Summary_2").Range("J7:J78") **Drop-down list 1
    For Each cell_2 In Worksheets("CAE Summary_2").Range("K7:K15") **Drop-down list 2

    With wsSummary
    .Range("D2").Value = cell.Value
    .Range("D4").Value = cell_2.Value
    .ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=FilePath & cell.Value & "_" & cell_2.Value & "_CAE.pdf", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
    End With
    Next cell_2
    Next cell

    Set wsSummary = Nothing
    End Sub


    Thanks!
    Will

    (P.S. If I could somehow consolidate all the pdfs into one workbook using the script, that would be awesome!)

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

    Re: Run-time error '1004' HELP

    Set wsSummary = Sheets("CAE Summary_2")
    For Each cell In Worksheets("CAE Summary_2").Range("J7:J78") **Drop-down list 1
    For Each cell_2 In Worksheets("CAE Summary_2").Range("K7:K15") **Drop-down list 2
    as all the cells are in the same workbook this would be better like
    Code:
    Set wsSummary = Sheets("CAE Summary_2")
    with wsSummary    ' move this line up to here
      For Each cell In .Range("J7:J78") 'Drop-down list 1
      For Each cell_2 In .Range("K7:K15") 'Drop-down list 2
    1004 is a fairly generic error and could apply to all sorts of issues
    on which line does the error occur?
    does filepath variable have a value?
    do your variables contain enough path separators (\) to a valid path ?


    With wsSummary
    .Range("D2").Value = cell.Value
    .Range("D4").Value = cell_2.Value
    the values in these cells will be overwritten for every file name?
    as far as i can see, this would be the only difference in the pdf files outputted, i am not sure that i understand the object of this code


    consolidate all the pdfs into one workbook using the script,
    how would you have 576 pdf files in a workbook?
    displayed, or just links?
    what are you envisaging?
    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

  3. #3

    Thread Starter
    New Member
    Join Date
    Dec 2017
    Posts
    3

    Re: Run-time error '1004' HELP

    Thank you for the response, this is helpful.

    I have made the small change you suggested and agree. I believe the path is not the issue. See the changes below with an indicator as to where the error occurs.

    Sub PDF()
    Dim cell As Range
    Dim cell_2 As Range
    Dim wsSummary As Worksheet

    Set wsSummary = Sheets("CAE Summary_2")
    With wsSummary
    For Each cell In Worksheets("CAE Summary_2").Range("J7:J78")
    For Each cell_2 In Worksheets("CAE Summary_2").Range("K7:K15")

    .Range("D2").Value = cell.Value
    .Range("D4").Value = cell_2.Value

    .ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=FilePath & cell.Value & "_" & cell_2.Value & "_CAE.pdf", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False *Error occurs here

    Next cell_2
    Next cell
    End With

    Set wsSummary = Nothing
    End Sub

    I was able to successfully run a prior version on this script with one drop down and am attempting to build it out further to accompany a second drop down. I'd like for it to iterate through all combinations of lists 1 and 2 and print separate pdfs for each iteration.

    Once that is completed, I'd like to see if I can build into the script a way to consolidate all the separate pdf outputs into one file for display. Please let me know if you have further questions as to what I am envisioning.

    Thanks again for your help.

    Will

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

    Re: Run-time error '1004' HELP

    i still believe it is a path issue
    make a small change here for testing
    Code:
    .Range("D2").Value = cell.Value
    .Range("D4").Value = cell_2.Value
    fname = FilePath & cell.Value & "_" & cell_2.Value & "_CAE.pdf"
    debug.print fname
    .ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=fname, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False *Error occurs here
    if you put a breakpoint on the line .exportas.........
    you can look in the immediate window to see the actual filepath\name that you are trying to save to, make sure it is a valid path, as expected and paste the returned output in a post here

    breakpoint click in the left margin breakpoint is set when a red dot
    immediate window from view menu or Ctrl G

    use code tags when posting code
    I have made the small change you suggested
    well actually you didn't, you only move the with wssummary line
    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

  5. #5

    Thread Starter
    New Member
    Join Date
    Dec 2017
    Posts
    3

    Re: Run-time error '1004' HELP

    Thank you! You were correct, the filename was incorrect as I had misspelled a one of the words...

    Appreciate the push to getting this to work!

    Will

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