Activating a workbook

  • Thread starter Thread starter Jeff Wright
  • Start date Start date
J

Jeff Wright

I am working on a macro which opens a workbook ("Info"), while another one
("Original") is already active. My macro successfully opens "Info", and
copies data from it to "Original". However, when the macro attempts to
reactivate "Info" in order to copy additional data, the macro halts with a
"subscript our of range" error. Below is the code:


' This routine asks for a file name and opens it

Answer = InputBox(Prompt:="Enter the File Name") '(for example, user
will type in "Info")
RetrievedFile = "C:\My Documents\Jeff's Documents\Excel Files\" + Answer
+ ".xls"
Workbooks.Open Filename:=RetrievedFile
Range("C1").Select
Selection.Copy
Windows("Original").Activate
Range("I1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Open
Workbooks(RetrievedFile).Activate

The last line above halts the macro and results in a "Subscript out of
range" error.

Help!!!
 
If you have a macro that deals with multi-workbook, it is a good practice to
assign a variable for each workbook name. The RetrievedFile is not workbook
name but workbook filepath.

That said, you'd better add the following line after "Workbooks.Open..."
statement:

Dim Temp_Workbook as string
Temp_WorkBook = ActiveWorkbook.name

Then, when you want to activate it, write:
Workbooks(Temp_WorkBook).Activate


This should solve the problem

Regards,
Michael
 
Hi Jeff

Use some code like


Dim wNewBook As Workbook
Dim wOldBook As Workbook

Set wOldBook = ActiveWorkbook
Set wBook = Workbooks.Open(Filename:=RetrievedFile)

Workbooks("Original.xls").Sheets(1).Range("I1") = _
wBook.Sheets(1).Range("C1").Value

You may need to change the sheet index numbers.

You should also use the GetOpenFile Name Method to prevent typos, e.g

Sub RetrieveFileName()
Dim sFileName As String

'Show the open dialog and pass the selected _
file name to the String variable "sFileName"
sFileName = Application.GetOpenFilename
'They have cancelled.
If sFileName = "False" Then Exit Sub

MsgBox sFileName
End Sub


** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
 
Thanks, guys!!

Both of your suggestions worked quite well, and my boss will now be happy
(I'll give you and this group full credit)!!

Regards,

Jeff
 
Back
Top