Copy data from Closed Workbook
 

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

Copy data from Closed Workbook
 

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 Bruins site)? I see that we are
using SQL in the second example, which is ok, but Im not sure it is
necessary. Or, is it?


Thanks in advance,
Ryan---

--
RyGuy


"Héctor Miguel" wrote:

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

Copy data from Closed Workbook
 

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


"ryguy7272" wrote in message ...
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 Bruins site)? I see that we are
using SQL in the second example, which is ok, but Im not sure it is
necessary. Or, is it?


Thanks in advance,
Ryan---

--
RyGuy


"Héctor Miguel" wrote:

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