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---


--
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

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


"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
>
>
>

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" <(E-Mail Removed)> wrote in message news7EA6EAD-A651-4732-BA70-(E-Mail Removed)...
> 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
>
>
> "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
>>
>>
>>