Detect flash drive letter, save file as PDF

[Willardio]

Hello all,

The following code is supposed to detect the letter of the flash drive if one is plugged in, then save the active workbook in both .xlsm and .pdf formats:

Sub usbSave()
On Error Resume Next
Dim i As Integer
Dim PDFfileName As String

Set fs = CreateObject("Scripting.FileSystemObject")
For i = 67 To 90
Set drspec = fs.GetDrive(Chr(i))
If drspec.drivetype = 1 Then
'PDFfileName = Range("H5").Value
 MsgBox "File saved to USB Drive (" & Chr(i) & ":\)"
spath = Chr(i) & ":\"
sFilename = Range("H5").Value & ".xlsm"
PDFfileName = spath & Range("H5").Value & ".pdf"
ActiveWorkbook.SaveCopyAs spath & sFilename
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=PDFfileName, openafterpublish:=False

i = 90

End If
Next i
End Sub

The .xlsm feature works, the .pdf part does not. Any ideas?

Best,

Willardio

[JieJenn]

Get rid of .pdf in this line


PDFfileName = spath & Range("H5").Value & ".pdf"

[Willardio]

thanks for the suggestion JieJenn, but it didn't make a difference. Still no PDF file saved on the flash.

[JieJenn]

Maybe remove On Error Resume next line and step through each code to see where the error occurs.

[Leith Ross]

Hello Willardio,

You need to check that the drive is available before you save the file. I have made some changes to your macro. Try this out and let me know the results.

Sub usbSave()
 
    Dim drspec      As Object
    Dim fs          As Object
    Dim i           As Integer
    Dim PDFfileName As String
    Dim sPath       As String

        Set fs = CreateObject("Scripting.FileSystemObject")

            For i = 67 To 90
                On Error Resume Next
                Set drspec = fs.GetDrive(Chr(i))
                    If drspec.DriveType = 1 And drspec.IsReady Then
                        MsgBox "File saved to USB Drive (" & Chr(i) & ":\)"
                        sPath = Chr(i) & ":\"
                        sFilename = Range("H5").Value & ".xlsm"
                        PDFfileName = sPath & Range("H5").Value & ".pdf"
                        ActiveWorkbook.SaveCopyAs sPath & sFilename
                        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfileName, openafterpublish:=False
                        Exit Sub
                    End If
                On Error GoTo 0
            Next i
            
        MsgBox "No Removable Drives were Accessible or Plugged In."
        
End Sub