run time error 9

[SandyUK]

Hello all

A while back I had to quickly put together a routine to take the
information from a main workbook and copy and save aspects of the data
into many different workbooks using a second workbook as a template. At
the time I recorded a macro to deal with the repetitive stuff which
meant that the workbook names of the workbook containing the data and
the template where embedded in the code. I am trying to tidy it up by
using the code below to specify the main workbook and the template.

Sub GetFilePathCopyTo()

Dim Finfo As String
Dim FilterIndex As Integer
Dim Title As String

' Set up list of file filters
Finfo = "Text Files (*.txt),*.txt," & _
"Lotus Files (*.prn),*.prn," & _
"Comma Separated Files (*.csv),*.csv," & _
"ASCII Files (*.asc),*.asc," & _
"All Files (*.*),*.*"

' Display *.* by default
FilterIndex = 5

' Set the dialog box caption
Title = "Select a File to Copy To"

' Get the filename
FilePathCopyTo = Application.GetOpenFilename(Finfo, _
FilterIndex, Title)

' Handle return info from dialog box
If FilePathCopyTo = False Then
MsgBox "No file was selected."
Else
MsgBox "You selected " & FilePathCopyTo
End If

End Sub


This works fine and I changed the next bit of code in the process from


Sub OpenRequote()

Workbooks.Open Filename:= _
"I:\Costings\Development\2005 - 2006\Requote\requote
template.xls"
Range("B6").Select
Windows("SAStanCosts WE 11 19 05.xls").Activate

End Sub


To this so that the hard coded workbook name was replaced


Sub OpenRequote()

Workbooks.Open FileName:= FilePathCopyTo
Range("B6").Select
Windows(FilePathCopyTo).Activate

End Sub


I am getting a run time error 9 when the code gets to
Windows(FilePathCopyTo).Activate and I don’t know what to do, can
anybody help?

Regards

Adrian

 

[Bob Phillips]

Sandy,

Have you declared FilePathToCopyTo as a module level variable? Otherwise, it
will pick up a procedure level variable in each procedure, a different one
each time, and it may be blank.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[SandyUK]

Thanks for the reply Bob

If I am understanding you correctly you are asking if i have declared
the variable as Public?

If so yes i have if not could you give me a little more detail?

Thanks

Adrian

 

[Bob Phillips]

Well it wasn't necessarily public Sandy, but just declared, and at module
level (at least).

I couldn't see in the code where the open routine was called. One got the
filename, one opened it, one got the file, but which calls which, and how.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Jeff Standen]

I think the problem is that FilePathCopyTo is a full path, and
Window.Activate just needs the filename. However, the window with that file
should be active anyway, because it's the one you just opened. I think you
mean to refer to a different file (going by your original code). You're
probably better off activating the workbook instead of the window - if you
declare a workbook variable and set it to the workbook you can then refer to
it later.

Unless that it, the workbook you are trying to activate is the one with the
code in (don't know if this is the case). Then you can just use
ThisWorkbook.

Jeff

 

[SandyUK]

Thanks for the replies Bob and Jeff

Jeff you have the hit the nail on the head and simplified the problem,
which is that i have a procdure that switches between 2 open workbooks
using there workbook names which at present is hard coded (as below)

Windows("SAStanCosts WE 11 19 05.xls").Activate

I am trying to change it so the user specifies the 2 workbooks to use.
I have captured the file path for each workbook but don't know how to
use it to make it work in my code as if it was the hard coded workbook
name?

Regards

Adrian

 

[Bob Phillips]

I would set two workbook object variables, and set each as I open them. Easy
to switch between them then.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Jeff Standen]

Something like this is what I was referring to.

Dim wbkStan as Workbook

Workbooks.open(filepath in here)
set wbkStan = activeworkbook

Jeff

 

[SandyUK]

Hi Bob

I think I understand the theory of what you are suggesting and I have
searched the site for workbook object variables but with no joy. Any
chance you could dumb it down for me or provide a little more detail?

Adrian

 

[Bob Phillips]

Something like this

Dim oWB1 As Workbook
Dim oWB2 As Workbook

Set oWB1 = Workbooks.Open("C:\MyTest\File1.xls")

'do some stuff

Set oWB2 = Workbooks.Open("C:\OtherDir\Subdir\File2.xls")

'now you have references to the 2 workbooks
'no need to activate, switch, or such nonsense

'now use them

Call DoSomething(oWB1)

Call DoSomething(oWB2)

...

Sub DoSomething(oWB As Workbook)
MsgBox oWB.Name
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[SandyUK]

Thanks Bob

That is spot on, solved my problem and i have learnt something new
..

Regards

Adrian

 

[Bob Phillips]

Excellent, and apologies for calling you Sandy earlier. I remember now, you
are Adrian from Sandy Beds <g>

Bob