Can i speed up this code

[Godders199]

Hello, I have the following code, which can take upto 3 minutes to run , depending on the number of rows in the spreadsheet( normally around 3000). There are two elements i am not sure off, I have read that copy paste is not the most efficient code to use, but cannot find examples of alternative solutions.

Secondly , I cannot find a way to purely copy the filtered rows and insert directly into access. you will see my current solution effectively copies into a blank spreadsheet and then access pulls the information from there.

Just looking at any code which will reduce the running time.
Sub save_allocation()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayStatusBar = False
Application.EnableEvents = False
' Allocationdump Macro
'Dim xm As Long
'
Sheets("submissions").Select
If Sheets("submissions").FilterMode Then Sheets("submissions").ShowAllData
Cells.Select
ActiveSheet.Range("A:ac").AutoFilter Field:=18, Criteria1:="<>"


Range("a2:z2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Workbooks.Open Filename:= _
"S:\4th Floor\SQ Operations\OPs Mi Spreadsheets\Allocation audit tools\2018\history.xlsx"

Range("a1").Select
xm = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("a" & xm).PasteSpecial xlPasteValues

ActiveWorkbook.Save
ActiveWorkbook.Close
Sheets("submissions").ShowAllData
Sheets("instructions").Select


'add allocated cases to access'
Dim appAccess As Object
Set appAccess = CreateObject("Access.Application")
appAccess.opencurrentdatabase "S:\4th Floor\SQ Operations\OPs Mi Spreadsheets\Allocation audit tools\2018\Allocation history1.accdb"
appAccess.Visible = False
appAccess.Run "importexcelspreadsheet"
appAccess.Quit
Set appAccess = Nothing

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayStatusBar = True
Application.EnableEvents = True

End Sub

 

[bsquad]

my guess would be your "importexcelspreadsheet" macro - which would be in the accdb file, post that. What you have above the MS Access - honestly there's realistically probably 100 different ways you could do it, but what you have I think is fine.

 

[mumps]

UNTESTED but give it a try. You don't have to select a range to copy it or to paste to it.

Sub save_allocation()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Dim LastRow As Long
    LastRow = Sheets("submissions").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ' Allocationdump Macro
    'Dim xm As Long
    '
    Sheets("submissions").Range("A1:AC" & LastRow).AutoFilter Field:=18, Criteria1:="<>"
    Sheets("submissions").Range("A2:Z" & LastRow).SpecialCells(xlCellTypeVisible).Copy
    Workbooks.Open Filename:="S:\4th Floor\SQ Operations\OPs Mi Spreadsheets\Allocation audit tools\2018\history.xlsx"
    Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Sheets("submissions").ShowAllData
    Sheets("instructions").Select
    
    'add allocated cases to access'
    Dim appAccess As Object
    Set appAccess = CreateObject("Access.Application")
    appAccess.opencurrentdatabase "S:\4th Floor\SQ Operations\OPs Mi Spreadsheets\Allocation audit tools\2018\Allocation history1.accdb"
    appAccess.Visible = False
    appAccess.Run "importexcelspreadsheet"
    appAccess.Quit
    Set appAccess = Nothing
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
End Sub

 

[bsquad]

[COLOR=#333333]'add allocated cases to access'[/COLOR]
[COLOR=#333333]Dim appAccess As Object[/COLOR]
[COLOR=#333333]Set appAccess = CreateObject("Access.Application")[/COLOR]
[COLOR=#333333]appAccess.opencurrentdatabase "S:\4th Floor\SQ Operations\OPs Mi Spreadsheets\Allocation audit tools\2018\Allocation history1.accdb"[/COLOR]
[COLOR=#333333]appAccess.Visible = False[/COLOR]
[COLOR=#ff0000]appAccess.Run "importexcelspreadsheet"[/COLOR]
[COLOR=#333333]appAccess.Quit[/COLOR]
[COLOR=#333333]Set appAccess = Nothing[/COLOR]

that is a macro itself, and has separate code. What you had posted was just the same code again. What the appAccess.Run "importexcelspreadsheet" is doing is calling another piece of code that is in the MS Access file >> Allocation history1.accdb

 

[Godders199]

Thanks bsquad.
How do i get the rows into access if i dont run this part of the code? The excel spreadsheet is cleared on completion of the task , if i link excel and access it just removed all the rows in access when i clear the data from excel.

 

[bsquad]

This stuff isn't easy first starting out so don't worry about it - I wasn't saying to not run that part; what I am asking is if you can post that part. With Visual Basic / VBA / Macros you can write a piece of code and then "Call" it. For an example - with the code in your first post the title - Sub save_allocation() you can Call that code to run; so it would essentially look like this

Call save_allocation 
or 
Application.Run "save_allocation"

so for specifically in your case because it is in Microsoft Access it is

appAccess.Run "importexcelspreadsheet"

with that, I am saying I believe (from looking at what you had posted) the code that is taking the longest is embedded in your Microsoft Access file and would need you to post that code. How you would retrieve it, is just like you did in your Excel file - in Microsoft Access 2013, I believe the Visual Basic button is under Database Tools, ALT+F11 like Excel should also work. Let me know if this is still not making sense