VBA Code - Refer to cell for sheet name

[tlc53]

Hi,
Is there a way to amend the below code so that instead of referring to the sheet name (AAI01) it picks it up from the current sheet, cell A1. So cell A1 contains "AAI01".
Thank you.

Private Sub CheckBox1_Click()
  If CheckBox1 Then
    Call SavePdf1("AAI01")
    CheckBox1.Value = True
  End If
End Sub

 

[Kenneth Hobson]

SavePdf1 [A1]

 

[tlc53]

SavePdf1 [A1]

Thanks but this didn't work. I then tried

Call SavePdf1("A12")

but this highlights an error down in the second part of the code, in particular, this line..

  Sheets(wSheet).ExportAsFixedFormat Type:=xlTypePDF, Filename:=wfolder & wfile, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

Here's the entire code (note, A12 reference rather than A1 in this actual example..

Private Sub CheckBox1_Click()
  If CheckBox1 Then
    Call SavePdf1("A12")
    CheckBox1.Value = True
  End If
End Sub


Sub SavePdf1(wSheet As String)
  Dim wfolder As String, wfile As String
  wfolder = Range("K12").Value
  wfile = Range("L12").Value
  If wfolder = "" Then
    MsgBox "Enter folder"
    Exit Sub
  End If
  If wfile = "" Then
    MsgBox "Enter file name"
    Exit Sub
  End If
  If Dir(wfolder, vbDirectory) = "" Then
    MsgBox "Folder does not exists"
    Exit Sub
  End If
  If Right(wfolder, 1) <> "\" Then wfolder = wfolder & "\"
  If Right(wfile, 4) <> ".pdf" Then wfile = wfile & ".pdf"
  Sheets(wSheet).ExportAsFixedFormat Type:=xlTypePDF, Filename:=wfolder & wfile, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  MsgBox "The file has been saved"
  CheckBox1.Enabled = False
  Range("J12").Value = "Saved " & Date
End Sub

 

[Kenneth Hobson]

Whether you pass [A1], the value that is in cell A1, or "AAI01" directly does not matter. It does not matter if you used Call or inferred call as I did. What matters is that that sheet name passed to the routine exists.The next thing that matters is that your drive:\folder exists. Lastly, did the routine build a legal Windows filename.

The filename is the usual problem. The problem is that it or part of it is a date sometimes. The date might have "/" characters or such which means folders to Windows. It looks like it checks for empty strings already.

The routine is also coded to get cell values from the activesheet. That may or may not be what is needed.

It looks like you checked if folder exists. Now, check that the full filename is legal. Debug.Print puts the results into the VBE Immediate window after a run. Enable it in the View menu if not open. MsgBox is a quick way to debug code as well. Sometimes, I need to work with the Immediate window results directly to debug.

MsgBox wfolder & wfile
Debug.Print wfolder & wfile
'Sheets(wSheet).ExportAsFixedFormat Type:=xlTypePDF, Filename:=wfolder & wfile, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

 

[tlc53]

A12 contains the word "Cordis".

This works perfectly and saves the file as expected..

Private Sub CheckBox1_Click()
  If CheckBox1 Then
    Call SavePdf1("Cordis")
    CheckBox1.Value = True
  End If
End Sub


Sub SavePdf1(wSheet As String)
  Dim wfolder As String, wfile As String
  wfolder = Range("K12").Value
  wfile = Range("L12").Value
  If wfolder = "" Then
    MsgBox "Enter folder"
    Exit Sub
  End If
  If wfile = "" Then
    MsgBox "Enter file name"
    Exit Sub
  End If
  If Dir(wfolder, vbDirectory) = "" Then
    MsgBox "Folder does not exists"
    Exit Sub
  End If
  If Right(wfolder, 1) <> "\" Then wfolder = wfolder & "\"
  If Right(wfile, 4) <> ".pdf" Then wfile = wfile & ".pdf"
  Sheets(wSheet).ExportAsFixedFormat Type:=xlTypePDF, Filename:=wfolder & wfile, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  MsgBox "The file has been saved"
  CheckBox1.Enabled = False
  Range("J12").Value = "Saved " & Date
End Sub

The only change I have made, is the "Call SavePdf1("A12")", which results in an error..

Private Sub CheckBox1_Click()
  If CheckBox1 Then
    Call SavePdf1("A12")
    CheckBox1.Value = True
  End If
End Sub


Sub SavePdf1(wSheet As String)
  Dim wfolder As String, wfile As String
  wfolder = Range("K12").Value
  wfile = Range("L12").Value
  If wfolder = "" Then
    MsgBox "Enter folder"
    Exit Sub
  End If
  If wfile = "" Then
    MsgBox "Enter file name"
    Exit Sub
  End If
  If Dir(wfolder, vbDirectory) = "" Then
    MsgBox "Folder does not exists"
    Exit Sub
  End If
  If Right(wfolder, 1) <> "\" Then wfolder = wfolder & "\"
  If Right(wfile, 4) <> ".pdf" Then wfile = wfile & ".pdf"
  Sheets(wSheet).ExportAsFixedFormat Type:=xlTypePDF, Filename:=wfolder & wfile, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  MsgBox "The file has been saved"
  CheckBox1.Enabled = False
  Range("J12").Value = "Saved " & Date
End Sub

Note tab name reference has changed as I'm working on an older version of my spreadsheet to try and get this working.

I figure that if the first code works fine with the actual tab name stated, then it must have something to do with the cell referencing. Being able to refer to the tab name by the cell would be really beneficial, so I'm hoping there's a way around it.

Could it have something to do with my () rather than []. I tried the square brackets but it wouldn't accept it.

 

[Akuini]

How about:

Call SavePdf1(Range("A12"))

 

[tlc53]

How about:

Call SavePdf1(Range("A12"))

Yay! You did it! Thank you

 

[Kenneth Hobson]

When you use Call, you must use ()'s. The way I called your routine, it does not need ()'s.

[]'s means Evaluate(). So, [A12]=Range("A12"). Since your routine expects a string even though [A12] or Range("A12") is passed, it uses the default property for a range object, Value. Had your routine required a range, those would have worked too.

These are all equal for your routine:

Call SavePdf1(Range("A12"))
SavePdf1 Range("A12")
Call SavePdf1([A12])
[COLOR="#FF0000"]SavePdf1 [A12][/COLOR]

Call SavePdf1(Range("A12").Value)
SavePdf1 Range("A12").Value
Call SavePdf1([A12].Value)
SavePdf1 [A12].Value

Call SavePdf1(Range("A12").Value2)
SavePdf1 Range("A12").Value2
Call SavePdf1([A12].Value2)
SavePdf1 [A12].Value2