Is it true that Excel allows you to read a workbook without having it open?
If so, I want to be able to write a macro that read a closed xls data file,
open another xls and insert the data into a given worksheet and a given cell
(like starting in B2 rather than A1). Last step is to save the file and close
both files. The problem is that I don't know how big is the data file. It
could be 200K or 1MB. Is it best to use copy and paste special? Or should I
read line by line?

Thanks for the help
M

"matelot" skrev i meddelandet
...
Is it true that Excel allows you to read a workbook without having it
open?

No, you have to open it

If so, I want to be able to write a macro that read a closed xls data
file,
open another xls and insert the data into a given worksheet and a given
cell
(like starting in B2 rather than A1). Last step is to save the file and
close
both files. The problem is that I don't know how big is the data file. It
could be 200K or 1MB. Is it best to use copy and paste special?

Copy and Paste Special should be a lot faster

Or should I
read line by line?

Thanks for the help
M

/Fredrik

Maybe something like this. This opens the destination wb, specifies the
destination cell range and enters formulas that reference the source wb. The
source wb is not opened. The formula results are then converted to values.

Note that the "A1" in the line
r.Formula = "='" & P & "\[" & FN & "]Sheet1'!A1"
will increment "A2", "A3" ... etc for each cell in range r. So a loop isn't
necessary.

Sub TransferData()
Dim r As Range
Dim ws As Worksheet
Dim wb As Workbook
Dim P As String, FN As String

FN = Application.GetOpenFilename("Excel Files(*.xls), *.xls")
Set wb = Workbooks.Open(FN) 'Destination wb
Set ws = wb.Sheets(1) 'Destination ws
Set r = ws.Range("A1:A100") 'Destination cell range
P = wb.Path 'Assumed path of source wb same as destination wb
FN = "SouceWB.xls" 'Substitute name of source wb
r.Formula = "='" & P & "\[" & FN & "]Sheet1'!A1"
r.Value = r.Value 'Transform r formulas to values
wb.Close True 'Close destination wb
End Sub

Regards,
Greg


"matelot" wrote:

Is it true that Excel allows you to read a workbook without having it open?
If so, I want to be able to write a macro that read a closed xls data file,
open another xls and insert the data into a given worksheet and a given cell
(like starting in B2 rather than A1). Last step is to save the file and close
both files. The problem is that I don't know how big is the data file. It
could be 200K or 1MB. Is it best to use copy and paste special? Or should I
read line by line?

Thanks for the help
M

Greg,
That's really cool! However, I don't know the # of rows of data I need to
copy from the source file. How would you capture the last row from the source
file and be able to plug in the destination cell range to something like set
r=ws.range("A1:A"&lastrow).
Is it doable?

"Greg Wilson" wrote:

Maybe something like this. This opens the destination wb, specifies the
destination cell range and enters formulas that reference the source wb. The
source wb is not opened. The formula results are then converted to values.

Note that the "A1" in the line
r.Formula = "='" & P & "\[" & FN & "]Sheet1'!A1"
will increment "A2", "A3" ... etc for each cell in range r. So a loop isn't
necessary.

Sub TransferData()
Dim r As Range
Dim ws As Worksheet
Dim wb As Workbook
Dim P As String, FN As String

FN = Application.GetOpenFilename("Excel Files(*.xls), *.xls")
Set wb = Workbooks.Open(FN) 'Destination wb
Set ws = wb.Sheets(1) 'Destination ws
Set r = ws.Range("A1:A100") 'Destination cell range
P = wb.Path 'Assumed path of source wb same as destination wb
FN = "SouceWB.xls" 'Substitute name of source wb
r.Formula = "='" & P & "\[" & FN & "]Sheet1'!A1"
r.Value = r.Value 'Transform r formulas to values
wb.Close True 'Close destination wb
End Sub

Regards,
Greg


"matelot" wrote:

Is it true that Excel allows you to read a workbook without having it open?
If so, I want to be able to write a macro that read a closed xls data file,
open another xls and insert the data into a given worksheet and a given cell
(like starting in B2 rather than A1). Last step is to save the file and close
both files. The problem is that I don't know how big is the data file. It
could be 200K or 1MB. Is it best to use copy and paste special? Or should I
read line by line?

Thanks for the help
M

You can't determine from a closed wb the number rows of data in a range
directly. All you can do is link to the wb using formulas as I have done.
However, you can assume that there will be at the very most, say 10000 rows
of data, and have the code apply formulas to a range of cells in the
destination wb that reference these 10000 cells. In other words, instead of
using A1:A100 as in my previous example, use A1:A10000.

You must also adjust the formula so that it will return blank ("") if the
referenced cell in the source wb is blank (""). Then, after converting the
formula to values, the formulas that returned blank will instead just be
blank. And after saving and reopening the destination wb the used range will
only be the range containing nonblank values instead of all the way down to
row 10000 (so the scroll bar won't be shrunken).

You could reference the entire column instead of just A1:A10000 but I would
expect a performance problem.

Example code:

Sub TransferData()
Dim r As Range
Dim ws As Worksheet
Dim wb As Workbook
Dim P As String
Dim FN As Variant

FN = Application.GetOpenFilename("Excel Files(*.xls), *.xls")
If FN = False Then Exit Sub
Set wb = Workbooks.Open(FN) 'Destination wb
Set ws = wb.Sheets(1) 'Destination ws
Set r = ws.Range("A1:A10000") 'Destination cell range
P = wb.Path 'Assumed path of source wb same as destination wb
FN = "SourceWB.xls" 'Name of source wb
r.Formula = "=If('" & P & "\[" & FN & "]Sheet1'!A1=" & _
""""", """", '" & P & "\[" & FN & "]Sheet1'!A1)"
r.Value = r.Value 'Transform r1 formulas to values
wb.Close True 'Close destination wb
End Sub

Hope it works.

Regards,
Greg

Go to Ron deBruins site. He has EXACLY what you
require, with a sample wb.
--
Robert


"Greg Wilson" wrote:

You can't determine from a closed wb the number rows of data in a range
directly. All you can do is link to the wb using formulas as I have done.
However, you can assume that there will be at the very most, say 10000 rows
of data, and have the code apply formulas to a range of cells in the
destination wb that reference these 10000 cells. In other words, instead of
using A1:A100 as in my previous example, use A1:A10000.

You must also adjust the formula so that it will return blank ("") if the
referenced cell in the source wb is blank (""). Then, after converting the
formula to values, the formulas that returned blank will instead just be
blank. And after saving and reopening the destination wb the used range will
only be the range containing nonblank values instead of all the way down to
row 10000 (so the scroll bar won't be shrunken).

You could reference the entire column instead of just A1:A10000 but I would
expect a performance problem.

Example code:

Sub TransferData()
Dim r As Range
Dim ws As Worksheet
Dim wb As Workbook
Dim P As String
Dim FN As Variant

FN = Application.GetOpenFilename("Excel Files(*.xls), *.xls")
If FN = False Then Exit Sub
Set wb = Workbooks.Open(FN) 'Destination wb
Set ws = wb.Sheets(1) 'Destination ws
Set r = ws.Range("A1:A10000") 'Destination cell range
P = wb.Path 'Assumed path of source wb same as destination wb
FN = "SourceWB.xls" 'Name of source wb
r.Formula = "=If('" & P & "\[" & FN & "]Sheet1'!A1=" & _
""""", """", '" & P & "\[" & FN & "]Sheet1'!A1)"
r.Value = r.Value 'Transform r1 formulas to values
wb.Close True 'Close destination wb
End Sub

Hope it works.

Regards,
Greg

Thanks for the response.

"Greg Wilson" wrote:

You can't determine from a closed wb the number rows of data in a range
directly. All you can do is link to the wb using formulas as I have done.
However, you can assume that there will be at the very most, say 10000 rows
of data, and have the code apply formulas to a range of cells in the
destination wb that reference these 10000 cells. In other words, instead of
using A1:A100 as in my previous example, use A1:A10000.

You must also adjust the formula so that it will return blank ("") if the
referenced cell in the source wb is blank (""). Then, after converting the
formula to values, the formulas that returned blank will instead just be
blank. And after saving and reopening the destination wb the used range will
only be the range containing nonblank values instead of all the way down to
row 10000 (so the scroll bar won't be shrunken).

You could reference the entire column instead of just A1:A10000 but I would
expect a performance problem.

Example code:

Sub TransferData()
Dim r As Range
Dim ws As Worksheet
Dim wb As Workbook
Dim P As String
Dim FN As Variant

FN = Application.GetOpenFilename("Excel Files(*.xls), *.xls")
If FN = False Then Exit Sub
Set wb = Workbooks.Open(FN) 'Destination wb
Set ws = wb.Sheets(1) 'Destination ws
Set r = ws.Range("A1:A10000") 'Destination cell range
P = wb.Path 'Assumed path of source wb same as destination wb
FN = "SourceWB.xls" 'Name of source wb
r.Formula = "=If('" & P & "\[" & FN & "]Sheet1'!A1=" & _
""""", """", '" & P & "\[" & FN & "]Sheet1'!A1)"
r.Value = r.Value 'Transform r1 formulas to values
wb.Close True 'Close destination wb
End Sub

Hope it works.

Regards,
Greg