Hello,
I was wondering if we do something wrong or can my code use an update
Because if we download a file from our sharepoint
And we copy that file to our temp folder
And when we readout the file to copy all the data to our desire sheet
It takes ages....
Codes i use are down here.
Thank you
Reading the file into the sheet:
Sub ReadingFile()
Dim ws As Worksheet
Dim objFSO As Object
Dim objFile As Object
Dim Dbfile As String
Set ws = ThisWorkbook.Sheets("DB_V")
Dim objConnection As ADODB.connection
Dim objRecordset As ADODB.Recordset
Dbfile = "https://sharepoint.com/sites/Database/Data.xlsx"
If DownloadFile(Dbfile, "C:\temp\" & "Data.xlsx") Then
' Increase Speed/Remove Screen Flicker
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Dbfile = "C:\Temp\Data.xlsx"
On Error Resume Next
Set objFile = objFSO.GetFile(Dbfile)
On Error GoTo 0
DoEvents
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Dbfile & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=0"";"
Set objRecordset.ActiveConnection = Nothing
objRecordset.Open "Select * FROM [DB_V$]", objConnection, adOpenForwardOnly, adLockReadOnly
If Not (objRecordset.BOF And objRecordset.EOF) Then
'copy all data
ws.Range("A2").CopyFromRecordset objRecordset
End If
objRecordset.Close
objConnection.Close
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationAutomatic
End If
End Sub
Download File:
Function DownloadFile(sUrl As String, filePath As String) As Boolean
Dim oXHTTP As Object
Dim oStream As Object
Dim MySize
Dim mystatus
Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
Set oStream = CreateObject("ADODB.Stream")
On Error Resume Next
oXHTTP.Open "GET", sUrl, False
oXHTTP.send
On Error GoTo 0
mystatus = oXHTTP.responsebody
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
DoEvents
With oStream
.Type = 1 'adTypeBinary
.Open
.Write oXHTTP.responsebody
.SaveToFile filePath, 2 'adSaveCreateOverWrite
.Close
End With
DoEvents
Set oXHTTP = Nothing
Set oStream = Nothing
DownloadFile = True
' Restore Application Settings
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationAutomatic
End Function