Some Idea using tables in sharepoint

[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

 

[Silversee]

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 think there may be some confusion over what you mean by data in SharePoint and your use of the term "tables".

At first I assumed you were talking about SharePoint list data. Access can directly import from or create links to lists in SharePoint Online

But from the above statement, it sounds like you are not accessing SharePoint lists, but copying CSV files from a SharePoint document library to your local folder?

Is the document library synced to your device? If so, you should be able to use some simple VBA code to copy the files from one location to the other.

You can also directly import from CSV files into Access tables. You can save your import definitions so that they can be run again whenever you like, and you can automate that using VBA.

Without knowing more about your specific process, it's hard to give more concrete advice, but I am confident there are a number of ways to do what you want.

 

[LeoM]

Thank you, i will check once i can reach the website, since is blocked in the office

 

[LeoM]

I think there may be some confusion over what you mean by data in SharePoint and your use of the term "tables".

At first I assumed you were talking about SharePoint list data. Access can directly import from or create links to lists in SharePoint Online

But from the above statement, it sounds like you are not accessing SharePoint lists, but copying CSV files from a SharePoint document library to your local folder?

Is the document library synced to your device? If so, you should be able to use some simple VBA code to copy the files from one location to the other.

You can also directly import from CSV files into Access tables. You can save your import definitions so that they can be run again whenever you like, and you can automate that using VBA.

Without knowing more about your specific process, it's hard to give more concrete advice, but I am confident there are a number of ways to do what you want.

Thank you, for the answer. Yes, correct, i don't have access to Sharepoint list and i have to copy from Sharepoint document library to local folder.
And yes the library is synced so probably could be link trough VBA but i really don't know how to do. I tried to get the link from one of the file, but looks like not allow, i don't know how to do. If you have way to do, i really appreciate if you can share some example of either linking direct in the database or any else can solve my trouble.
Cheers.

 

[LeoM]

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

I tested the script but, as also the one suggested by Gasman, the imported file has only "STRANGE" characters in the first raw. Attached the 2 files (original and the one I obtained but the script). Cheers