Working With Multiple Workbooks

[R**1944]

I am new to XCEL VBA and need some help. What I am trying to do may not be possible since I can't find it in manuals or forums, but here goes.

I'm trying to bring up multiple .xls files at the same time and switch back and forth between files to execute code and transfer small bits of data between the files.

I'm getting the "subscript out of range" error with the code below.

Any help would be appreciated.

Thanks

Ron


[VBA]
Public WKBA As Variant
Public WKBB As Variant


Sub GetMultipleWorkbooks()



WKBA = ActiveWorkbook.FullName

Call GetImportFileName(Filename)
WKBB = Filename
Workbooks.Open Filename:=WKBB

Workbooks(WKBA).Activate

' execute code

Workbooks(WKBB).Activate

' execute code



End Sub
[/VBA]
[VBA]
Sub GetImportFileName(Filename)

Dim Filt As String
Dim FilterIndex As Integer
Dim Title As String


' Set up list of filters
Filt = "Text Files (*.txt),*.txt," & _
"Lotus Files (*.prn),*.prn," & _
"Comma Separated Files (*.csv),*.csv," & _
"All Files (*.*),*.*," & _
"Excel Files (*.xls),*.xls"


' Display *.* by default
FilterIndex = 5
' Set the dialog box caption
Title = "Select File to Import"
' Get the file name
Filename = Application.GetOpenFilename _
(FileFilter:=Filt, _
FilterIndex:=FilterIndex, _
Title:=Title)

' Exit if dialog box canceled
If Filename = False Then
MsgBox "No file was selected."
Exit Sub
End If

' Display full path and name of file
MsgBox "You selected : " & Filename



End Sub
[/VBA]

.
.

Edit Lucas: Ron, if you select your code when posting and hit the vba button it will format your code for the forum.

[chungtinhlak]

[vba]
Dim workbook1 as workbook, workbook2 as workbook, workbook3....

'make the macro code to run on workbook1, at any section you want to switch workbook, you can activate different workbook

workbook1.activate
[/vba]

'switch back and fourth that way. i'm sure other member here are much more advance then I am but that's how I do it.

[lucas]

I'm trying to bring up multiple .xls files at the same time and switch back and forth between files to execute code and transfer small bits of data between the files.

I'm getting the "subscript out of range" error with the code below.

subscript out of range means it can't find it......

Why don't you just tell us exactly what you are trying to do between these workbooks and how many you are dealing with and see if there is a better solution.

Give a step by step of what you want to do and what you want to happen.

[R**1944]

Lucas,

Step 1, I have a file1 open.

Step 2, I need to open file2 to pull information out of it to update info in file1.

Step 3, After I update the info in file 1, I will close file2.

Thanks for your help.

Ron

[lucas]

Ron, one last question and we can get some code to help you. Is the range you are updating just a cell or a couple of cells, or a range or mult ranges or a named range, or is it an entire sheet?

[R**1944]

Lucas,

I will be copying about 10 cells from one file to the other.

By the way, file2 needs to be selected by the user.

Thanks for your help.

Ron

[lucas]

Try this:
[VBA]Option Explicit
Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
Dim sFileName As String
Application.ScreenUpdating = False ' turn off the screen updating

'Make path selections below
sFileName = Application.GetOpenFilename
'They have cancelled.
If sFileName = "False" Then Exit Sub
Set wb = Workbooks.Open(sFileName)
' Set wb = Workbooks.Open("f:\Temp\Social Club.xls", True, True)
' Set wb = Workbooks.Open(ActiveWorkbook.Path & "\Social Club.xls")
' the sheet in this workbook to copy to
With ThisWorkbook.Worksheets("Final Results")
' read data from the source workbook
'the range to copy to in this workbook-name of sheet to copy FROM-range in closed workbook to copy
.Range("B8").Formula = wb.Worksheets("RESULTS").Range("B7").Formula
.Range("R8").Formula = wb.Worksheets("RESULTS").Range("R7").Formula

End With
wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub
[/VBA]

wb is the workbook you are opening and copying from.
This tells it to copy from what range and what sheet in wb workbook:
[VBA]wb.Worksheets("RESULTS").Range("B7").Formula[/VBA]

The with statement tells it what sheet to copy to in the open workbook:
[VBA]With ThisWorkbook.Worksheets("Final Results")[/VBA]

[R**1944]

Lucas,

Sorry, but I wasn't clear about one thing. When I open the WB file, there is a subroutine in the WB file that will extract the information that I want and copy it to "this workbook" ( the original file that was open). I couldn't figure out how to modify what you did to make this happen. When I was in this workbook, it couldn't find the subroutine that is in the WB file. I hope this is clear. Can your procedure be modified to do this?

Thanks again for your help.

Ron

[lucas]

can you post two sample workbooks without any important data?

hit post reply at the bottom left of the last post and then write your message, then scroll down till you find the button that says "manage attachments"

So, just so I understand, you have a first workbook open, you want to open a second workbook with data that also has the routine to copy data to the first workbook....then close the second workbook?

Why can't it be in the first workbook......can it if we can alter it to get the data you want?

[lucas]

mark them somehow so we know which one is open and which one we need to open after.

[R**1944]

Lucas,

These files are I think, too large to post with sensitive information.

Your understanding of the problem is correct.

I can copy the subroutine from the second workbook into the first workbook without a problem.

Can you tell me how to make that work?

Thanks again.

Ron

[R**1944]

Lucas,

I got it worked out tonite. I copied the routine to the first workbook and it worked with a few modifications. It's not pretty but it works.

Thanks for all your help.

Ron

[lucas]

Good deal Ron, If you need any more help post here. Mark this thread solved when you get done using the thread tools at the top of the page.