0

I have a workbook with dashboards that are printed to pdf using VBA (see code below). This used to work pretty good up till a couple of weeks ago. I dont get any error messages but on some occasions the export is worng. When i run the code again without changing anything in the workbook the export succeeds. It seems like some random issue maybe connected to my workstation performance, i have switched laptops recently (altough the new one should be better).

Does anybody know why this goes wrong and what i could do about it

Sub PDF_maken()
    Dim ws As Worksheet
    Dim LR As Long
    Dim r As Range
    Dim Mypath As String
    Dim Mypath2 As String
    Dim strarray As String
    Dim test As String
    Dim strItem As String

    Set ws = ThisWorkbook.Worksheets("Printen")
    LR = ws.Cells(Rows.Count, 1).End(xlUp).Row

    Mypath2 = ws.Range("H1").Text

    If Dir(Mypath2, vbDirectory) = "" Then
        MkDir Mypath2
    End If

    For Each r In ws.Range("B4:B19").Cells
        strItem = ws.Range("C" & r.Row).Text
        strItem = Replace(strItem, "~", "_")
        strItem = Replace(strItem, Chr(34), "_")
        strItem = Replace(strItem, "%", "_")
        strItem = Replace(strItem, "#", "_")
        strItem = Replace(strItem, "&", "_")
        strItem = Replace(strItem, "*", "_")
        strItem = Replace(strItem, ":", "_")
        strItem = Replace(strItem, ",", "_")
        strItem = Replace(strItem, "<", "_")
        strItem = Replace(strItem, ">", "_")
        strItem = Replace(strItem, "?", "_")
        strItem = Replace(strItem, "{", "_")
        strItem = Replace(strItem, "}", "_")
        strItem = Replace(strItem, "|", "_")
        strItem = Replace(strItem, "/", "_")
    
        If Not IsEmpty("B" & r.Row) Then
            Mypath = ws.Range("B" & r.Row).Text
    
            If Dir(Mypath, vbDirectory) = "" Then
                MkDir Mypath
            End If
    
            strarray = ""
            colCheck = 4
            Do Until Cells(r.Row, colCheck) = ""
                strarray = strarray & IIf(colCheck > 4, ",", "") & Cells(r.Row, colCheck).Value
                colCheck = colCheck + 1
            Loop    
    
            If InStr(1, strarray, ",") Then
                Dim MyAr As Variant
                MyAr = Split(strarray, ",") '<~~ This is where we are creating an actual array
        
                ThisWorkbook.Sheets(MyAr).Select
                ThisWorkbook.Sheets(ws.Range("D" & r.Row).Text).Activate
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                  Mypath & strItem & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                  IgnorePrintAreas:=False, OpenAfterPublish:=False
            Else
                ThisWorkbook.Sheets(strarray).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                  Mypath & strItem & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                  IgnorePrintAreas:=False, OpenAfterPublish:=False
            End If
        End If
        ws.Activate
    Next r
End Sub

Picture 1: Good export

Good export

Picture 2: Failed export

Failed export

Gangula
  • 5,193
  • 4
  • 30
  • 59
user4373888
  • 53
  • 1
  • 12
  • Note that `ActiveWorkbook` is the workbook that has focus (is on top) and that can change easily eg. by a mouse click. Use `ThisWorkbook` which is the workbook the code is written in (and does not change). • Can you [edit] your question and show the entire procedure for exporting (not only one line)? The issue might be somewhere else in the code (unless it is a bug in Excel). – Pᴇʜ Oct 12 '21 at 09:13
  • Post edited and using Thisworkbook instead of Activeworkbook – user4373888 Oct 12 '21 at 11:31
  • Please read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). This might also be an issue. Get rid of all `Select` and `Activate` statements as well as `ActiveSheet`. Instead address your sheets directly e.g. `ThisWorkbook.Sheets(ws.Range("D" & r.Row).Text).ExportAsFixedFormat …`, Note that the active sheet can easly change by a mouse click and then your code addresses the wrong sheet. So make sure not to use `ActiveSheet`. Please check if this solves the issue. – Pᴇʜ Oct 12 '21 at 11:38
  • Besides that I see nothing obviosly wrong that could produce such an effect that it sometimes works and sometimes doesn't. Things like `ActiveSheet` and `Select` are well known to cause such effects because selection and active sheet can change easily. – Pᴇʜ Oct 12 '21 at 11:45
  • Where i have could, i have changed the code, will report back when it fails again. (does not happen every time i run the code) – user4373888 Oct 12 '21 at 12:00

0 Answers0