Excel save as csv on Sharepoint with VBA

[MichielS340]

Hi,

Hopefully somenone knows how to do the following. I have the following script to create a csv file and store it locally. Now I want to save the csv file on Sharepoint but I am not able to create the right path for it. 

How would I need to construct the myCSVfilename_helprequired in order to have it saved directly in a Sharepoint directory?

Many thanks,

Michiel

Option Explicit

Sub ExportData()
    Dim myCSVFileName As String
    Dim fNum As Integer
    Dim csvVal As String
    Dim i As Long
    Dim j As Integer
    Dim fileNumber
    Dim vaData As Variant
    
    vaData = Blad1.Range("B2:I27").Value
    
    myCSVFileName = ThisWorkbook.Path & "/test.csv"
    
    myCSVFileName_HelpRequired = "https://company.sharepoint.com/sites/DTCM/Gedeelde%20documenten/General/Forecast%20actueel/DummyName/DummyName" & "/test.csv"

    csvVal = ""
    fNum = FreeFile
    
    Open myCSVFileName For Output As #fNum
    For i = 1 To UBound(vaData)
        For j = 1 To UBound(vaData, 2)
            Select Case j
                Case 1, 3, 4
                    csvVal = csvVal & Chr(34) & vaData(i, j) & Chr(34) & ","
                Case 2, 7
                    csvVal = csvVal & Format(vaData(i, j), "0") & ","
                Case 5, 8
                    csvVal = csvVal & Format(vaData(i, j), "yyyy-mm-dd") & ","
                Case 6
                    csvVal = csvVal & Replace(Format(vaData(i, j), "0.00"), ",", ".") & ","
            End Select
        Next j
        Print #fNum, Left(csvVal, Len(csvVal) - 1)
        csvVal = ""
    Next i
    
    Close #fNum
End Sub

[#2 · Accepted solution]

If you are able to assign a drive letter to the sharepoint library you can keep your current VBA code. See: https://m365scripts.com/sharepoint-online/how-to-map-a-network-drive-to-sharepoint-library/

[#3]

thanks, I was hoping an easier approach (in the sense of no powershell) was possible...