Create a new folder on Google Drive using Excel VBA Coding

Hi everyone,

I am a fairly new user to Excel vba. I am trying to create for my company a macro enabled excel sheet which will save itself as a macro free sheet as well as a pdf in the new folder that it will make on the Google Drive. I am using the following code below, but it is:
(i) not creating the new folder on Google Drive
(ii) not naming the excel sheet as it does the pdf

Please help me on this, I am quite frustrated as I have spent a lot of days trying to figure my way out. Thanks in advance (my code is below).


Sub SaveOnGoogleDrive()

' print pdf file

Dim WSHShell As Object
Dim strPath As String
Dim sFileName As String
Dim sFullPath As String

' get Documents folder path (16)
Set WSHShell = CreateObject("Wscript.Shell")
strPath = WSHShell.SpecialFolders(16)
Set WSHShell = Nothing
' change to "Google Drive"
strPath = Replace(strPath, "Documents", "Google Drive\Purchase Order\Test PO & Tracker\")


Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next

' set current workbook file name
sFileName = Range("F6:F6") & "-" & Range("B12:B12") & "(" & Range("B10:B10") & ")" & "-" & Range("B6:B6")
' change to pdf file name: replace ".xlsm" by criterion + ".pdf"
sFileName = Replace(sFileName, ".xlsm", " " & "Test" & ".pdf")
' note: the above won't work if the workbook has not been saved

' create full path name
sFullPath = strPath & Application.PathSeparator & sFileName

With ActiveSheet
' save as a PDF file
' minimum quality to keep the file small
.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:=sFullPath, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Worksheets(Array("Sheet1")).Copy
ActiveWorkbook.SaveAs FileName:=strPath & "\" & sFileName & ".xlsx"

End With

On Error GoTo 0
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub