Some Idea using tables in sharepoint (1 Viewer)

[LeoM]

Good morning, everyone.
I'm little on trouble and i need your kindly suggestion wherever is possible.
This is the scenario: I have a database which has some tables linked to some csv files located in my C drive (specifically C:\Local). Up to now all good.
The original csv files are daily updated in a share point folder so, every day, I manually copy the tables from the share point to my C:\Local folder in order to update my database.
I'm looking a way (VBA probably) to avoid such manual work. In my mind (but only in my mind) i think to a function (to be add in Autoexec Macro) to automatically synchronize my C:\Local with share point, but no idea how to do and if it is the correct way. If any have experience of similar cases, i really appreciate if you can suggest me. Hope i have been clear.
Cheers

 

[Gasman]

I do not have sharepoint, so cannot test, but this is what ChatGPT offered for 'vba to sync sharepoint csv files to pc'

Sub SyncCSVFromSharePoint()
    Dim sharepointURL As String
    Dim localPath As String
    Dim fileName As String
    Dim fullSPFilePath As String
    Dim fullLocalFilePath As String

    ' SharePoint folder URL and file name
    sharepointURL = "https://yourcompany.sharepoint.com/sites/yoursite/Shared Documents/"
    fileName = "data.csv"
    fullSPFilePath = sharepointURL & fileName

    ' Local path where you want to save the file
    localPath = "C:\LocalFolder\"
    fullLocalFilePath = localPath & fileName

    ' Create XMLHTTP object
    Dim httpRequest As Object
    Set httpRequest = CreateObject("MSXML2.XMLHTTP")

    ' Send HTTP GET request
    httpRequest.Open "GET", fullSPFilePath, False
    httpRequest.setRequestHeader "Cache-Control", "no-cache"
    httpRequest.Send

    ' Check if request was successful
    If httpRequest.Status = 200 Then
        ' Create stream to write binary data
        Dim stream As Object
        Set stream = CreateObject("ADODB.Stream")
        stream.Type = 1 ' Binary
        stream.Open
        stream.Write httpRequest.responseBody
        stream.SaveToFile fullLocalFilePath, 2 ' 1=overwrite, 2=create new
        stream.Close

        MsgBox "File downloaded successfully to: " & fullLocalFilePath
    Else
        MsgBox "Failed to download file. HTTP Status: " & httpRequest.Status
    End If
End Sub

To sync CSV files from a SharePoint document library to a local PC using VBA, you can use the WebDAV path of the SharePoint library. SharePoint allows access via mapped drives or UNC paths if WebDAV is enabled.

Prerequisites:​

1. You must have access to the SharePoint site.
2. The SharePoint library must be synced with OneDrive or accessible via UNC path like:
   https://yoursharepointsite/sites/yoursite/Shared Documents/filename.csv
3. You need to have the correct permissions to download files.

 

[LeoM]

Thank you. Unfortunately, although this procedure creates an empty file (only first raw of the file is filled with many random characters). So probably there is something not correct.

 

[Gasman]

Well I only treat anything from an AI as the starting point.
It also saves me writing a lot of code up front. It generally has the logic though.

Walk the code, line by line and inspect what you get. That is how I approach any code I write anyway.

As it is Sharepoint, only you and your company can test this.
Are you able to use FTP on sharepoint?, that could be another way?

 

[LeoM]

I really appreciate your support and thanks for this. Actually, I have only SharePoint and i never used in all my past years' experience.
I went through the code step by step, but it seems the problem is that it doesn't care about the contain of the CSV file, or better, data are not filled in the new file. Really don't know where else to look. The link to the file should be correct (i get the link from sharepoint) but don't know why data are not imported in the new file. Cheers

 

[Gasman]

Are you able to give access at all then? Is there a public section somewhere?

 

[Gasman]

Have a read here.
Try changing the Type.

Edit: I think there is a missing Read there?, after the open?
Try commenting out the Type line, which will then default to Text.

 

[LeoM]

I used Type 1 (Binary), changing to Type 2 (text) gives error to the line "stream.Write httpRequest.responseBody" with the following message: Runtime error 3219 - Operation is not allowed in this context.
Cheers

 

[Gasman]

If you looked at that link, it says that you need ReadText and WriteText for text?
That code had no read even?

However I am just going through what I would try as I mentioned no Sharepoint to test on, and no public ones appear to exist.
It looks like that could work for any url?, but I cannot get to my website file where I uploaded a csv as a test.

 

[theDBguy]

Maybe look into using Power Flow or take a look at this article.

Download a File from the Internet

To download a file from the Internet, one option is to use the URLDownloadToFile API, which looks something like this. #If VBA7 Then Priv...

thedbguy.blogspot.com