Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Reading a copied excel file

0

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
Answer
Discuss

Discussion

Hi Ghost. Not sure on this one but notice that your line:
If DownloadFile(Dbfile,... 
calls the function which ends by cancelling the speed-up statements for Application.ScreenUpdating etc. (so later bits of your main macro might be slower) . 

Have you recorded the time taken by each portion of the code to see what takes longest?
John_Ru (rep: 6142) Mar 11, '22 at 11:57 am
Hi John_Ru
No i didn't not even known how... to do that
GhostofWanted (rep: 46) Mar 11, '22 at 12:49 pm
Ghost

To work out the times, put this at the very start of your main macro:
Dim lStart As Double
lStart = Timer

... then at say two or three points in your macro, add these two lines:
' print time between named points:
Debug.Print "From Start to A  = " & (Timer - lStart) & " seconds"
lStart = Timer

but change the text bit in bold to "A to B  = " etc. Then look in VB Explorer's Intermediate window (where the Debug.Print lines will print out) the timings.

If you move those lines (points A,B, C etc.) around, you may find the bit (line) which causes the biggest delay.
John_Ru (rep: 6142) Mar 11, '22 at 1:12 pm
Thx for the code
Results:
From Start to A To N  = 20,828125 seconds

if i remove those lines
    Application.ScreenUpdating = False '   
'Application.EnableEvents = False '   
'Application.DisplayStatusBar = False '   
'Application.Calculation = xlCalculationManual    
Application.DisplayAlerts = False  

i get From Start A To N  = 26,8046875 seconds


I hope it can be much faster some way?
GhostofWanted (rep: 46) Mar 11, '22 at 1:16 pm
Sorry but I have no time to test your code. Did you move the second set of timer  lines to find which parts take the longest (with the speed-up features working)? Perhaps work from the end of the main macro backwards...
John_Ru (rep: 6142) Mar 11, '22 at 2:05 pm
Hi John_Ru,
i have tested all the codes
The longest is where we read the file into our DB_V sheet
under 
ws.Range("A2").CopyFromRecordset objRecordset
End If

it takes From Start to A to N  = 136,337890625 seconds
And under
    objRecordset.Open "Select * FROM [DB_Veilingen$]", objConnection, adOpenForwardOnly, adLockReadOnly
From Start to A  to N = 27,0703125 seconds
So i guess we have to find a solution for this line
ws.Range("A2").CopyFromRecordset objRecordset
because its more then 136 seconds

the download code
is between 33 til 2.67 seconds
GhostofWanted (rep: 46) Mar 11, '22 at 4:38 pm
Ghost. Your timings are a bit confusing but I don't think I can help further, sorry.

Your problem line 
ws.Range("A2").CopyFromRecordset objRecordset
pastes data and will be slower if ScreenUpdating is enabled- that's why I suggested you remove the lines:

 
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.DisplayStatusBar = True
    Application.Calculation = xlCalculationAutomatic
in your function (since the problem line is after that and screen updating would be True as your function is written). You can see the effect if you run this from a module in an empty file: 
Sub test()
Application.EnableEvents = False
Debug.Print "Sub:" & Application.EnableEvents
 
Funk ("x")
 
Debug.Print "Sub:" & Application.EnableEvents
Application.EnableEvents = True
Debug.Print "Sub:" & Application.EnableEvents
End Sub
 
 
Function Funk(Stg As String)
 
Application.EnableEvents = False
 
Debug.Print "Func:" & Application.EnableEvents
 
Application.EnableEvents = True
Debug.Print "Func:" & Application.EnableEvents
 
End Function
i.e. the duplicated lines in the Function restore the features so any lines in your main macro will be slower.  
John_Ru (rep: 6142) Mar 12, '22 at 5:51 am
Hello John_Ru

Thanks for all the help you have provided to me But still removing all the line you suggested didn't make it better seems excel stuck for a quit of time and the timer go to 146 seconds no i have no idea why or how But thanks again i really appricait all your help ;) Guess i have to start over the code from 0 and see where it go wrong
GhostofWanted (rep: 46) Mar 12, '22 at 9:33 am
I have started a new sub
with new data

added this part

Dim objConnection As ADODB.connection
Dim objRecordset As ADODB.Recordset

Dim mySQL As String
Dim ws As Worksheet
Dim Dbfile As String
 
'set connection
Set objConnection = New ADODB.Connection
Set objRecordset = New ADODB.Recordset
Set ws = ThisWorkbook.Sheets("DB_V")
Dim lStart As Double
lStart = Timer
    
    Dbfile = "C:\Temp\Data.xlsx"
 
'sql select statement
mySQL = "SELECT * FROM [DB_V$];"
 
'open connection
With objConnection 
    .ConnectionString = _
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Dbfile & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=0"";"
    .Open
End With
 
'open table / recordset
objRecordset .Open mySQL, objConnection, adOpenForwardOnly, adLockReadOnly
 
'check if data exist for recordset
If Not (objRecordset.BOF And objRecordset.EOF) Then
    'copy data to cells
ws.Range("A2").CopyFromRecordset objRecordset 
'even tried thid line
'ws.Cells(2, 1).CopyFromRecordset objRecordset 
 
End If
 


but still its loads the data in 12 seconds so thats fast
but then when data are in my ws sheet
the code keeps running and excel got stuck like for 202 seconds
Any idea why the code keeps running?
how can i stop it if all data has been readed out
GhostofWanted (rep: 46) Mar 12, '22 at 10:37 am
Add to Discussion

Answers

0
Selected Answer

Problem solved

It seems i had records till 1048576 somehow in our xlsx file

Have to look into that if we export it to our sharepoint

But it loads in 12 sec without freezing like before

So thanks for the help ;)

Discuss

Discussion

Ghost. Glad you got it sorted. Note that 1,048,576 is Excel's maximum row limit (while it's 16,384 for columns) so it looks like your macro examines the maximnum number of rows (rather than actual records). I'm not near my PC for the next 2 days but suspect the problem is the line:
If Not (objRecordset.BOF And objRecordset.EOF) Then
since a record can't be both BOF and EOF so it just runs until the last row.
John_Ru (rep: 6142) Mar 13, '22 at 9:32 am
Hi John_Ru,

Thanks :)
Oh i see
if i use
If objRecordset.RecordCount > 0 Then 


will this be more corrected?
GhostofWanted (rep: 46) Mar 13, '22 at 9:35 am
Not sure, sorry Ghost. Not near my PC and it's been a long time since I extracted records like that. 
John_Ru (rep: 6142) Mar 13, '22 at 9:38 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login