R
RyanH
I have a workbook that I want to update from another workbook. I currently
open the data source workbook get the data then close it. It runs semi-slow
and I was wondering if there is a way to open the workbook hidden so the user
never sees it open in the task bar at the bottom of the screen? I figure
this would allow the macro to run faster.
Option Explicit
Private Sub btnUpdateParts_Click()
Dim wksJobCosting As Worksheet
Dim varSourceFilePath As Variant
Dim strSourceFileName As String
Dim lngLastRow As Long
Dim myArray As Variant
Dim CloseWorkbook As Boolean
Application.ScreenUpdating = False
' worksheet that recieves new data
Set wksJobCosting = ActiveWorkbook.Sheets("Parts List")
' delete old parts from list
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
If lngLastRow > 1 Then
Rows("2:" & lngLastRow).Delete
End If
' name of source data file, returns String or Boolean
varSourceFilePath = Application.GetOpenFilename("All Excel Files
(*.xls),*.xls")
' get file name from the file path
myArray = Split(varSourceFilePath, "\")
strSourceFileName = myArray(UBound(myArray))
If varSourceFilePath = False Then
' if user clicks cancel in the Open Dialog box
Exit Sub
Else
' disable Workbook_Open Event, then open data workbook
With Application
.EnableEvents = False
' check if source file is open already, error occurs if file is
not open
On Error Resume Next
If Workbooks(strSourceFileName) Is Nothing Then
On Error GoTo 0
CloseWorkbook = True
.Workbooks.Open FileName:=varSourceFilePath, ReadOnly:=True
End If
' copy source data, paste data in worksheet, close source workbook
With Workbooks(strSourceFileName)
lngLastRow = .Sheets("Parts List").Cells(Rows.Count,
"A").End(xlUp).Row
.Sheets("Parts List").Rows("2:" & lngLastRow).Copy
Destination:=wksJobCosting.Rows("2:2")
' close source workbook if it wasn't open before
If CloseWorkbook = True Then
.Close SaveChanges:=False
End If
End With
End With
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
open the data source workbook get the data then close it. It runs semi-slow
and I was wondering if there is a way to open the workbook hidden so the user
never sees it open in the task bar at the bottom of the screen? I figure
this would allow the macro to run faster.
Option Explicit
Private Sub btnUpdateParts_Click()
Dim wksJobCosting As Worksheet
Dim varSourceFilePath As Variant
Dim strSourceFileName As String
Dim lngLastRow As Long
Dim myArray As Variant
Dim CloseWorkbook As Boolean
Application.ScreenUpdating = False
' worksheet that recieves new data
Set wksJobCosting = ActiveWorkbook.Sheets("Parts List")
' delete old parts from list
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
If lngLastRow > 1 Then
Rows("2:" & lngLastRow).Delete
End If
' name of source data file, returns String or Boolean
varSourceFilePath = Application.GetOpenFilename("All Excel Files
(*.xls),*.xls")
' get file name from the file path
myArray = Split(varSourceFilePath, "\")
strSourceFileName = myArray(UBound(myArray))
If varSourceFilePath = False Then
' if user clicks cancel in the Open Dialog box
Exit Sub
Else
' disable Workbook_Open Event, then open data workbook
With Application
.EnableEvents = False
' check if source file is open already, error occurs if file is
not open
On Error Resume Next
If Workbooks(strSourceFileName) Is Nothing Then
On Error GoTo 0
CloseWorkbook = True
.Workbooks.Open FileName:=varSourceFilePath, ReadOnly:=True
End If
' copy source data, paste data in worksheet, close source workbook
With Workbooks(strSourceFileName)
lngLastRow = .Sheets("Parts List").Cells(Rows.Count,
"A").End(xlUp).Row
.Sheets("Parts List").Rows("2:" & lngLastRow).Copy
Destination:=wksJobCosting.Rows("2:2")
' close source workbook if it wasn't open before
If CloseWorkbook = True Then
.Close SaveChanges:=False
End If
End With
End With
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub