Error Opening Workbook

[Vacuum Sealed]

Hi All

I have been using this code for some time, the only difference today is that
I decided to move this particular code from 1 module to another.

And now it's throwing up an error at the start of this line:

FileToOpen

Variable not Defined


Sub ImportSchedData()

Dim SDataWb As Workbook, TDataWb As Workbook

ChDrive "T:\"
ChDir "T:\VIC\Scheduler"

FileToOpen = Application.GetOpenFilename _
(Title:="Select a TMS File to Import", _
FileFilter:="Excel Files *.xls (*.xls),")

If FileToOpen = False Then
MsgBox "No file specified.", vbExclamation, "Doh!!!"
Exit Sub
Else
Workbooks.Open Filename:=FileToOpen
End If

Set SDataWb = Workbooks.Open(Filename:=FileToOpen)
Set TDataWb = Workbooks("VicMaster.xls")

SDataWb.Sheets("Main").Range("A2:Q500").Copy

Windows("VicMaster.xls").Activate
Sheets("Master").Select
Range("A5").Select
ActiveSheet.Paste
Application.CutCopyMode = False

SDataWb.Close noSave

End Sub

II spent an hour on it trying different things, and it's got me beat.

Any thoughts
TIA
Mick.

 

[GS]

Vacuum Sealed wrote on 7/6/2011 :

Hi All

I have been using this code for some time, the only difference today is that
I decided to move this particular code from 1 module to another.

And now it's throwing up an error at the start of this line:

FileToOpen

Variable not Defined


Sub ImportSchedData()

Dim SDataWb As Workbook, TDataWb As Workbook

ChDrive "T:\"
ChDir "T:\VIC\Scheduler"

FileToOpen = Application.GetOpenFilename _
(Title:="Select a TMS File to Import", _
FileFilter:="Excel Files *.xls (*.xls),")

If FileToOpen = False Then
MsgBox "No file specified.", vbExclamation, "Doh!!!"
Exit Sub
Else
Workbooks.Open Filename:=FileToOpen
End If

Set SDataWb = Workbooks.Open(Filename:=FileToOpen)
Set TDataWb = Workbooks("VicMaster.xls")

SDataWb.Sheets("Main").Range("A2:Q500").Copy

Windows("VicMaster.xls").Activate
Sheets("Master").Select
Range("A5").Select
ActiveSheet.Paste
Application.CutCopyMode = False

SDataWb.Close noSave

End Sub

II spent an hour on it trying different things, and it's got me beat.

Any thoughts
TIA
Mick.

Dim FileToOpen As String???

I suspect you do have Option Explicit declared at the top of the module
and so VBA is looking for your definition of FileToOpen.

 

[Vacuum Sealed]

Thx Garry

Interestingly, never had it declared.

I use the exact same code (with the exception of the last three lines which
activate the source WB lastly, then closes it) in a different workbook,
again undeclared and it works problem free.

I tried your answer way before I posted this thread, I even tried Dimming it
as a variant.

As it is at work, I will have a go at with the declaration angle this time
and see what happens.

As work does not allow Newsgroups, I cannot access any responses til I get
home in roughly 12 hours.

Cheers
Mick.

 

[Vacuum Sealed]

Hi Garry

I did some more moving of codes again.

I move this particular code into it's own module and wollah..!!

Working as if nothing was ever wrong with it.

I did declare it, but then I had a follow-on issue where it stating that the
Object or Variable = nothing.

I removed the declaration and compiled it again and it works fine.

I have no idea, and will leave it at that and make sure for future reference
to never mix routines of this nature in the same module.

Cheers & thx again
Mick.