Copy data from Closed Workbook

[ryguy7272]

I am using this code:
http://www.rondebruin.nl/copy7.htm

Extremely useful!!

This is the sub I am using now:
Sub File_In_Local_Folder()
'***
End Sub

I am just wondering how to make the range in the closed Workbook variable.

I surmise that it would require something like this:
Sub File_In_Local_Folder()
Application.ScreenUpdating = False
On Error Resume Next
Dim Last As Long
Dim DestSh As Worksheet


Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "WeeklyForecastingAll"

With ActiveSheet
.Move after:=Worksheets(Worksheets.Count)
End With


Last = LastRow(DestSh)

'Call the macro GetRange
GetRange "C:\Documents and Settings\rshuell\Desktop\Weekly
Reporting\Friday", "WeeklyForecastingAll.xls", "WeeklyForecastingAll", Last =
LastRow(DestSh), _
Sheets("WeeklyForecastingAll").Range("A1")



On Error GoTo 0
Application.ScreenUpdating = True
End Sub


Sub GetRange(FilePath As String, FileName As String, SheetName As String, _
SourceRange As String, DestRange As Range)


Dim Start



'Go to the destination range
Application.Goto DestRange



'Resize the DestRange to the same size as the SourceRange
Set DestRange = DestRange.Resize(Range(SourceRange).Rows.Count, _
Range(SourceRange).Columns.Count)



'Add formula links to the closed file
With DestRange
.FormulaArray = "='" & FilePath & "/[" & FileName & "]" & SheetName _
& "'!" & SourceRange



'Wait
Start = Timer
Do While Timer < Start + 2
DoEvents
Loop



'Make values from the formulas
.Copy
.PasteSpecial xlPasteValues
.Cells(1).Select
Application.CutCopyMode = False
End With
End Sub

But, how do I get the variable rows, from the closed workbook, into the
GetRange variable? Am I just making this overly complicated? Is there an
easier way?

Thanks!!
Ryan---

 

[Héctor Miguel]

hi, Ryan !

did you tried using ADO ? (there are several ways), here is an example:
get in an (empty ?) activesheet all the data in "UsedRange" of the worksheet (from closed Wbk)

(important): mark a reference in your vba project to:
Microsoft ActiveX Data Objects x.x Library (x.x should it be version 2.8 ?)

Sub GetDataFromClosedWorkbook_ADO()
Dim adoConn As ADODB.Connection, rstData As ADODB.Recordset, _
strSQL As String, srcFolder As String, srcFile As String, srcSheet As String
srcFolder = "c:\documents and settings\rshuell\desktop\weekly reporting\friday\"
srcFile = "weeklyforecastingall.xls"
srcSheet = "weeklyforecastingall"
Set adoConn = New ADODB.Connection
adoConn.Open "driver={microsoft excel driver (*.xls)};driverId=790;readonly=true;dbq=" & srcFolder & srcFile & ";"
strSQL = "select * from [" & srcSheet & "$]"
Set rstData = New ADODB.Recordset
On Error Resume Next
rstData.Open strSQL, adoConn, adOpenForwardOnly, adLockReadOnly, adCmdText
Range("a1").CopyFromRecordset rstData
If rstData.State = adStateOpen Then rstData.Close
Set rstData = Nothing
adoConn.Close
Set adoConn = Nothing
End Sub

hth,
hector.

__ OP __

I am using this code: http://www.rondebruin.nl/copy7.htm
Extremely useful!!
This is the sub I am using now:
Sub File_In_Local_Folder()
'***
End Sub

I am just wondering how to make the range in the closed Workbook variable. (...)

But, how do I get the variable rows, from the closed workbook, into the GetRange variable?
Am I just making this overly complicated? Is there an easier way?
__ exposed code __
I surmise that it would require something like this:
Sub File_In_Local_Folder()
Application.ScreenUpdating = False
On Error Resume Next
Dim Last As Long
Dim DestSh As Worksheet
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "WeeklyForecastingAll"
With ActiveSheet
.Move after:=Worksheets(Worksheets.Count)
End With
Last = LastRow(DestSh)
'Call the macro GetRange
GetRange "C:\Documents and Settings\rshuell\Desktop\Weekly Reporting\Friday", "WeeklyForecastingAll.xls", "WeeklyForecastingAll", Last = LastRow(DestSh), _
Sheets("WeeklyForecastingAll").Range("A1")
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

Sub GetRange(FilePath As String, FileName As String, SheetName As String, _
SourceRange As String, DestRange As Range)
Dim Start
'Go to the destination range
Application.Goto DestRange
'Resize the DestRange to the same size as the SourceRange
Set DestRange = DestRange.Resize(Range(SourceRange).Rows.Count, _
Range(SourceRange).Columns.Count)
'Add formula links to the closed file
With DestRange
.FormulaArray = "='" & FilePath & "/[" & FileName & "]" & SheetName _
& "'!" & SourceRange
'Wait
Start = Timer
Do While Timer < Start + 2
DoEvents
Loop
'Make values from the formulas
.Copy
.PasteSpecial xlPasteValues
.Cells(1).Select
Application.CutCopyMode = False
End With
End Sub

 

[ryguy7272]

Thanks for the suggestion Héctor Miguel. I tried what you proposed, and set
a reference to Tools > References > Microsoft ActiveX Data Objects Recordset
2.8 Library. When I tried this at home yesterday, nothing happened; no error
occurred but no data was imported. Now, I am in the office, trying it again,
and I get an error that says ‘Compile Error: User-defined type not defined’.
What am I doing wrong? I liked the method that I suggested, and it worked,
but I get the predefined range imported. I really wanted to import a dynamic
range of data; a ‘used range’. Can I make a slight modification to the
original code (which I got from Ron de Bruin’s site)? I see that we are
using SQL in the second example, which is ok, but I’m not sure it is
necessary. Or, is it?


Thanks in advance,
Ryan---

--
RyGuy

hi, Ryan !

did you tried using ADO ? (there are several ways), here is an example:
get in an (empty ?) activesheet all the data in "UsedRange" of the worksheet (from closed Wbk)

(important): mark a reference in your vba project to:
Microsoft ActiveX Data Objects x.x Library (x.x should it be version 2.8 ?)

Sub GetDataFromClosedWorkbook_ADO()
Dim adoConn As ADODB.Connection, rstData As ADODB.Recordset, _
strSQL As String, srcFolder As String, srcFile As String, srcSheet As String
srcFolder = "c:\documents and settings\rshuell\desktop\weekly reporting\friday\"
srcFile = "weeklyforecastingall.xls"
srcSheet = "weeklyforecastingall"
Set adoConn = New ADODB.Connection
adoConn.Open "driver={microsoft excel driver (*.xls)};driverId=790;readonly=true;dbq=" & srcFolder & srcFile & ";"
strSQL = "select * from [" & srcSheet & "$]"
Set rstData = New ADODB.Recordset
On Error Resume Next
rstData.Open strSQL, adoConn, adOpenForwardOnly, adLockReadOnly, adCmdText
Range("a1").CopyFromRecordset rstData
If rstData.State = adStateOpen Then rstData.Close
Set rstData = Nothing
adoConn.Close
Set adoConn = Nothing
End Sub

hth,
hector.

__ OP __

I am using this code: http://www.rondebruin.nl/copy7.htm
Extremely useful!!
This is the sub I am using now:
Sub File_In_Local_Folder()
'***
End Sub

I am just wondering how to make the range in the closed Workbook variable. (...)

But, how do I get the variable rows, from the closed workbook, into the GetRange variable?
Am I just making this overly complicated? Is there an easier way?
__ exposed code __
I surmise that it would require something like this:
Sub File_In_Local_Folder()
Application.ScreenUpdating = False
On Error Resume Next
Dim Last As Long
Dim DestSh As Worksheet
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "WeeklyForecastingAll"
With ActiveSheet
.Move after:=Worksheets(Worksheets.Count)
End With
Last = LastRow(DestSh)
'Call the macro GetRange
GetRange "C:\Documents and Settings\rshuell\Desktop\Weekly Reporting\Friday", "WeeklyForecastingAll.xls", "WeeklyForecastingAll", Last = LastRow(DestSh), _
Sheets("WeeklyForecastingAll").Range("A1")
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

Sub GetRange(FilePath As String, FileName As String, SheetName As String, _
SourceRange As String, DestRange As Range)
Dim Start
'Go to the destination range
Application.Goto DestRange
'Resize the DestRange to the same size as the SourceRange
Set DestRange = DestRange.Resize(Range(SourceRange).Rows.Count, _
Range(SourceRange).Columns.Count)
'Add formula links to the closed file
With DestRange
.FormulaArray = "='" & FilePath & "/[" & FileName & "]" & SheetName _
& "'!" & SourceRange
'Wait
Start = Timer
Do While Timer < Start + 2
DoEvents
Loop
'Make values from the formulas
.Copy
.PasteSpecial xlPasteValues
.Cells(1).Select
Application.CutCopyMode = False
End With
End Sub

 

[Ron de Bruin]

Hi ryguy7272

Do not use ADO or the other code from my site if you want full control

Try the code or the RDBMerge add-in
http://www.rondebruin.nl/copy3.htm

You can use a named range if you want to use ADO
http://www.rondebruin.nl/ado.htm




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Thanks for the suggestion Héctor Miguel. I tried what you proposed, and set
a reference to Tools > References > Microsoft ActiveX Data Objects Recordset
2.8 Library. When I tried this at home yesterday, nothing happened; no error
occurred but no data was imported. Now, I am in the office, trying it again,
and I get an error that says ‘Compile Error: User-defined type not defined’.
What am I doing wrong? I liked the method that I suggested, and it worked,
but I get the predefined range imported. I really wanted to import a dynamic
range of data; a ‘used range’. Can I make a slight modification to the
original code (which I got from Ron de Bruin’s site)? I see that we are
using SQL in the second example, which is ok, but I’m not sure it is
necessary. Or, is it?


Thanks in advance,
Ryan---

--
RyGuy

hi, Ryan !

did you tried using ADO ? (there are several ways), here is an example:
get in an (empty ?) activesheet all the data in "UsedRange" of the worksheet (from closed Wbk)

(important): mark a reference in your vba project to:
Microsoft ActiveX Data Objects x.x Library (x.x should it be version 2.8 ?)

Sub GetDataFromClosedWorkbook_ADO()
Dim adoConn As ADODB.Connection, rstData As ADODB.Recordset, _
strSQL As String, srcFolder As String, srcFile As String, srcSheet As String
srcFolder = "c:\documents and settings\rshuell\desktop\weekly reporting\friday\"
srcFile = "weeklyforecastingall.xls"
srcSheet = "weeklyforecastingall"
Set adoConn = New ADODB.Connection
adoConn.Open "driver={microsoft excel driver (*.xls)};driverId=790;readonly=true;dbq=" & srcFolder & srcFile & ";"
strSQL = "select * from [" & srcSheet & "$]"
Set rstData = New ADODB.Recordset
On Error Resume Next
rstData.Open strSQL, adoConn, adOpenForwardOnly, adLockReadOnly, adCmdText
Range("a1").CopyFromRecordset rstData
If rstData.State = adStateOpen Then rstData.Close
Set rstData = Nothing
adoConn.Close
Set adoConn = Nothing
End Sub

hth,
hector.

__ OP __

I am using this code: http://www.rondebruin.nl/copy7.htm
Extremely useful!!
This is the sub I am using now:
Sub File_In_Local_Folder()
'***
End Sub

I am just wondering how to make the range in the closed Workbook variable. (...)

But, how do I get the variable rows, from the closed workbook, into the GetRange variable?
Am I just making this overly complicated? Is there an easier way?
__ exposed code __
I surmise that it would require something like this:
Sub File_In_Local_Folder()
Application.ScreenUpdating = False
On Error Resume Next
Dim Last As Long
Dim DestSh As Worksheet
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "WeeklyForecastingAll"
With ActiveSheet
.Move after:=Worksheets(Worksheets.Count)
End With
Last = LastRow(DestSh)
'Call the macro GetRange
GetRange "C:\Documents and Settings\rshuell\Desktop\Weekly Reporting\Friday", "WeeklyForecastingAll.xls",
"WeeklyForecastingAll", Last = LastRow(DestSh), _
Sheets("WeeklyForecastingAll").Range("A1")
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

Sub GetRange(FilePath As String, FileName As String, SheetName As String, _
SourceRange As String, DestRange As Range)
Dim Start
'Go to the destination range
Application.Goto DestRange
'Resize the DestRange to the same size as the SourceRange
Set DestRange = DestRange.Resize(Range(SourceRange).Rows.Count, _
Range(SourceRange).Columns.Count)
'Add formula links to the closed file
With DestRange
.FormulaArray = "='" & FilePath & "/[" & FileName & "]" & SheetName _
& "'!" & SourceRange
'Wait
Start = Timer
Do While Timer < Start + 2
DoEvents
Loop
'Make values from the formulas
.Copy
.PasteSpecial xlPasteValues
.Cells(1).Select
Application.CutCopyMode = False
End With
End Sub