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
--
Cheers,
Ryan

Give this a look:
http://www.rondebruin.nl/ado.htm


Regards,
Ryan---

--
RyGuy


"RyanH" wrote:

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
--
Cheers,
Ryan