PDA

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!

GTO
09-22-2010, 03:44 AM
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.

GTO
09-22-2010, 09:41 AM
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

GTO
09-26-2010, 09:16 PM
Glad to help :-) :friends: