View Full Version : Solved: Copying data from another workbook which name is unknown
NomalAnomaly
09-22-2010, 03:25 AM
Hi,
My situation is as follows:
I have 2 open workbooks and I'd like to copy data from one workbook to the other workbook. Assuming that I don't know the name of the workbook from which I want to copy the data, is there a simple way to do this?
Thanks!
Which is copying to which?
NomalAnomaly
09-22-2010, 04:33 AM
Copy data from the workbook which name is unknown, to the workbook which name is known.
lynnnow
09-22-2010, 05:11 AM
Why don't you try opening the unknown workbook from the known workbook and then the file name will be "known" to VBA
NomalAnomaly
09-22-2010, 05:13 AM
How can I open it if I can't refer to it by its name?
lynnnow
09-22-2010, 06:10 AM
Use the FileOpenDialog box to select the file and then transfer that value to a variable. Then VBA will know the name of the file.
Copy data from the workbook which name is unknown, to the workbook which name is known.
Okay, here is a very simple example. We could use a userform (see attached) with a listbox to list any other open workbooks.
The userform and code goes in the 'known' wb.
Option Explicit
Private Sub cmdCancel_Click()
Unload Me
End Sub
Private Sub cmdOK_Click()
Dim wb As Workbook
Set wb = Workbooks(lstOpenWBs.Value)
'do whatever copying
wb.Worksheets(1).Copy _
After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim wb As Workbook
cmdOK.Enabled = False
If Workbooks.Count > 1 Then
For Each wb In Workbooks
If Not wb.Name = ThisWorkbook.Name Then
lstOpenWBs.AddItem wb.Name
End If
Next
lstOpenWBs.ListIndex = 0
cmdOK.Enabled = True
Else
Me.Caption = "No Other Workbooks Open!"
End If
End Sub
Hope that helps,
Mark
NomalAnomaly
09-26-2010, 05:05 AM
Hi Mark
Thank you very much for your reply.
The above code will certainly do it.
NormalAnomaly
Glad to help :-) :friends:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.