CODEWITHSUNDEEP
excelvbapdf
dimauser
dimauser

Reputation: 7

VBA : use String for create a PDF file

I have a problem in creating a .pdf file out of different not predefined numbers of sheets.

Logic: Press Button1 - creates a new sheet (Employee x) e.g. you create 3 different sheets for your employees.

So, I want to create a .pdf file only out of these 3 sheets.

For-Loop gives me this String: "Worker1", "Worker2", "Worker3"

If I put this String (mystring) into :

Sheets(Array(**mystring**)).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "\Sales", _
    openafterpublish:=False, ignoreprintareas:=False

I get the Error: Run-time error '9': Subscript out of range.

Otherwise, If I insert the strings manually it works. But it's no predefined how many employees you have and what their name is.

Sheets(Array("Worker1", "Worker2", "Worker3", ... ,"Workerx")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "\Sales", _
        openafterpublish:=False, ignoreprintareas:=False

Some one any idea how to fix this?

Upvotes: 0

Views: 407

Answers (2)

Tim Williams
Tim Williams

Reputation: 166980

If you create the string as "Worker1,Worker2,Worker3" you can use Split(myString, ",") to create the array.

Something like this:

Option Explicit

Private Sub CommandButton1_Click()

    Dim stringname As String, wb As Workbook, ws As Worksheet, i As Long, sep As String
    
    Set wb = ThisWorkbook 'or ActiveWorkbook
    Set ws = wb.Worksheets("MitarbeiterListe")
    
    For i = 6 To 106
        If ws.Range("B" & i) <> "" Then
            stringname = stringname & sep & ws.Range("B" & i) & " " & ws.Range("C" & i)
            sep = "," 'populate separator after first item
        End If
    Next i
      
    MsgBox stringname
    
    ThisWorkbook.Sheets(Split(stringname, ",")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "\Sales", _
                                    openafterpublish:=False, ignoreprintareas:=False
       
    MsgBox "All PDF's have been successfully exported."

End Sub

Upvotes: 1

dimauser
dimauser

Reputation: 7

**FULLCODE**

Private Sub CommandButton1_Click()

Dim forename As String
Dim lastname As String
Dim fullname As String
Dim stringname As String

For i = 0 To 100

If Sheets("MitarbeiterListe").Range("B" & 6 + i) <> "" Then
    forename = Sheets("MitarbeiterListe").Range("B" & 6 + i)
    lastname = Sheets("MitarbeiterListe").Range("C" & 6 + i)
    fullname = Chr(34) & forename & " " & lastname & Chr(34)
    
    stringname = stringname + fullname & ", "
    
End If
Next i
    stringname = Left(stringname, Len(stringname) - 2)
    MsgBox stringname


Sheets(Array(stringname)).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "\Sales", _
openafterpublish:=False, ignoreprintareas:=False
   
MsgBox "All PDF's have been successfully exported."
End Sub

Upvotes: 0

Related Questions