Copy data from closed workbooks

[Ming]

I am trying to copy a lot of data from a closed Excel file to current
Excel file using a piece of VBA code.

I tried "copy and paste",got the following code from Macro recording:

Sub Dataacquire()
Dim Row As Integer

Workbooks.Open Filename:="C:\temp\test.xls"
Row = Application.Count("A:A")
'find the total rows of non-empty data
Range("A1:A1000").Select
'I want to apply the "Row" value here but don't know how
Selection.Copy
Workbooks("test.xls").Close
Windows("Book2.xls").Activate
Range("A1").Select
ActiveSheet.Paste
End Sub

Is there a more efficient way to get data from a closed file?
For example, I can use a simple loop to copy data between WORKSHEETS
such as
..Worksheets("Sheet2").Cells(i,j)=.WorkSheets("Sheet1").Cells(i,j). Can
I do similar thing between different WORKBOOKS?

After I open the source file, which workbook will "With ThisWorkbook"
statement refer to,source file or current one?

Thanks for your help!

 

[Héctor Miguel]

hi, Ming !

... trying to copy... data from a closed... to current Excel file using... VBA [...]
Sub Dataacquire()
Dim Row As Integer
Workbooks.Open Filename:="C:\temp\test.xls"
Row = Application.Count("A:A")
'find the total rows of non-empty data
Range("A1:A1000").Select
'I want to apply the "Row" value here but don't know how
Selection.Copy
Workbooks("test.xls").Close
Windows("Book2.xls").Activate
Range("A1").Select
ActiveSheet.Paste
End Sub
Is there a more efficient way to get data from a closed file? [...]
After I open the source file
which workbook will "With ThisWorkbook" statement refer to,source file or current one?

FWIW, 'ThisWorkbook' [always] refers to the source-code file
'current file' is [always] the 'ActiveWorkbook'
BTW, Range("a1") is a 'short-cut' of ActiveSheet.Range("a1")
which is also a 'short.cut' of ActiveWorkbook.ActiveSheet.Range("a1")
unless you 'qualify' other workbook/worksheet as in...
Worksheets("My other worksheet Active_OR_Not BUT in ActiveWorkbook").Range("a1") or...
Workbooks("My other workbook Active_OR_Not BUT OPEN").Worksheets(...).Range(...)

having said that, you might want to try with the following vba example...
Sub Dataacquire()
Workbooks.Open "c:\temp\test.xls"
Worksheets("sheet1").Range(Range("a1"), Range("a65536").End(xlUp)).Copy _
ThisWorkbook.Worksheets("sheet1").Range("a1")
ActiveWorkbook.Close SaveChanges:=False
End Sub

assuming Sheet1 [both workbooks] is the correct worksheet
hth,
hector.

 

[Dave Peterson]

First, I'd stay away from a variable named Row. VBA uses that--and it may not
confuse excel, but it sure would confuse me. Second, if you want to use a
variable that refers to a number of rows, it's better to use Long (not
integer). Especially, if your data can get large.

And if you assign a workbook variable to the workbook you're opening, you can
use that in your code.

This may give you some ideas:

Option Explicit
Sub DataAcquire2A()
Dim RngToCopy As Range
Dim wkbk As Workbook
Dim DestCell As Range
Dim myFileNames As Variant
Dim iCtr As Long
Dim testStr As String

Set DestCell = ThisWorkbook.Worksheets(1).Range("a1")

myFileNames = Array("C:\my documents\excel\book1.xls", _
"c:\my documents\excel\book2.xls")

For iCtr = LBound(myFileNames) To UBound(myFileNames)
testStr = ""
On Error Resume Next
testStr = Dir(myFileNames(iCtr))
On Error GoTo 0

If testStr = "" Then
MsgBox myFileNames(iCtr) & " doesn't exist!"
Else
Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr))
With wkbk.Worksheets(1)
Set RngToCopy = .Range("a1", _
.Cells(.Rows.Count, "A").End(xlUp))
End With

DestCell.Resize(RngToCopy.Rows.Count, _
RngToCopy.Columns.Count).Value _
= RngToCopy.Value

Set DestCell = DestCell.Offset(RngToCopy.Rows.Count, 0)

wkbk.Close savechanges:=False
End If
Next iCtr

End Sub

 

[Ming]

Hector, Dave

Thanks a lot for your help!

 

[wojo]

Will your example, allow the workbook to remain closed, and still get
data from it, for another workbook?

I have a linked (read-only) file, that I need to paste data to, from a
workbook that might be open (by another user) or might be closed.

Can I use code to retrieve the data (copy) and past it into the linked
workbook, if I don't know the status (open or closed) of the original
file?

 

[Dave Peterson]

It actually opens the workbook, retrieves some values and closes the workbook.

But you could do this with screenupdating off. So the end user may not even
know that it opened the other workbook.

If you need to paste data into a readonly workbook, then you're gonna have
trouble. But you could open the receiving workbook, paste the data, and save
and close the workbook.

This might give you an idea:

Option Explicit
Sub testme()

Dim wkbk As Workbook
Dim myPath As String
Dim myFileName As String
Dim TestStr As String
Dim WorkbookWasOpen As Boolean

myFileName = "book1.xls"
myPath = "C:\my documents\excel"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

TestStr = ""
On Error Resume Next
TestStr = Dir(myPath & myFileName)
On Error GoTo 0

If TestStr = "" Then
MsgBox myFileName & " doesn't exist in: " & myPath
Exit Sub
End If

Set wkbk = Nothing
On Error Resume Next
Set wkbk = Workbooks(myFileName)
On Error GoTo 0

WorkbookWasOpen = True
If wkbk Is Nothing Then
'that workbook is closed, so open it
Set wkbk = Workbooks.Open(Filename:=myPath & myFileName, _
ReadOnly:=False)
WorkbookWasOpen = False
Else
If LCase(wkbk.Path & "\") <> LCase(myPath) Then
MsgBox "You have a workbook with that name already open!" & _
vbLf & "Please close it and try again"
Exit Sub
Else
If wkbk.ReadOnly = True Then
MsgBox "Please open: " & myPath & myFileName & " not ReadOnly!"
Exit Sub
End If
End If
End If

'the workbook is open now (some way or another)
'do the work (copy and paste???)
'save it
wkbk.Save

'close it if wasn't open.
If WorkbookWasOpen = False Then
wkbk.Close savechanges:=False 'just saved the changes
End If

End Sub

 

[wojo]

Actually, I don't have to worry about saving the changes to the
readonly workbook, as I planned on this macro running when the readonly
is opened. As it will run each time, to retrieve the comments from the
first workbook (the macro actually deletes all comments from the read
only as the first step in the macro), pastes them into the readonly. I
don't need to save. This ensure the readonly (which has the data
linked) has the most current 'comments' when opened.

My concern is the 'status' of the workbook that I am opening. I need
to know if I can write the macro so that it doesn't matter if the
workbook is open already. I need the macro to Open if the original
workbook is closed or simply copy the cells if the workbook is open. I
want to avoid the message that say's "this workbook is already open".

If the workbook is closed, after the macro opens it, of course, I need
it to close the workbook.
Have I totally confused you? Hope not.

Jo

 

[Dave Peterson]

Take a look at that other code. You'll see that I checked for the workbook
already being open.