Closing "myfile" in VBA

[dancing fish]

Hi all,

VBA question.

I've opened up MyFile, copied some info, pasted it, and now i want t
close MyFile without saving changes. Myfile does not have a
application extension such as .xls.

This doesnt work because I don't know what I'm doing!

Windows("MyFile").Select
ActiveWindow.Close


Please help.

The full code is below:

Sub GetInfo()
'
' GetInfo Macro
' Macro recorded 10/01/2004 by Awyatt
'

'
Application.ScreenUpdating = False

Dim MyFile As String
Dim MyRange As String


Sheets("MyRange").Select
Range("d17").Select
MyRange = ActiveCell.Value

Sheets("File").Select
Range("B3").Select
MyFile = ActiveCell.Value


Workbooks.Open Filename:=MyFile, UpdateLinks:=3
Sheets("UK Price Retail").Select
Range(MyRange).Select
Selection.Copy

Windows("NewSystem.xls").Activate
Sheets("Costs").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False

Windows("MyFile").Select
ActiveWindow.Close
Windows("NewSystem.xls").Select
Sheets("Input").Select


End Su

 

[Bob Phillips]

Try This

Workbooks("MyFile").Close SaveChanges:=False

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[dancing fish]

Bob,

I tried that but I still get the same error message:

"Subscript out of range."


Any more ideas

 

[Rollin_Again]

If you are using a variable named* myFile* to represent the workbook i
question, you do NOT need quotation marks around the variable name whe
calling it.

Workbooks(MyFile).Close SaveChanges:=False

Rolli

 

[dancing fish]

Guys,

Still getting the same problem.

I'm wondering if it has something to do with the lack of applicatio
extension.

Myfile does not end with .xls.

Do you think it is getting lost and i need to create a new user define
variable

 

[Bob Phillips]

It certainly looks like it. For new workbooks, you can close it without the
file extension, but for previously saved files it seems to need it.

Where does the name come from, and where did the extension go? If it's an
Excel workbook, couldn't you just add .xls?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Rollin_Again]

How are you setting the value for the variable representing the workbook
that you want to close? When the active window contains the workbook
you want to close you can set the string value of the variable by using
*myFile = ActiveWorkbook.name*

After setting this variable just use the code I mentioned before

WORKBOOKS(MYFILE).CLOSE SAVECHANGES:=FALSE


I tested it this way and I was able to close the workbook out using my
code above using my variable name without the quotes.


If you still get an Error I would just re-create the workbook in
question and re-try your code.

Rollin

 

[BrianB]

' MyFile should not have quotes (you have defined the variable)
Windows(MyFile).Select
ActiveWindow.Close savechanges:=False
' better
Windows(MyFile).Close savechanges:=False

 

[dancing fish]

Bonza,

Cheers Rollin, that last bit worked a treat. Though i still think it i
a bit weird. I had to create another variable which i calle
myclosefile, this did not have the folder references that myfile has.
based myclosefile on the activeworkbook name after i had opened myfile
and eventually it worked. I presume there was some sourcing proble
created by the drive references. Anyway, the end code is as follows.

Thanks Again

DF.

Sub GetInfo()
'
' GetInfo Macro
' Macro recorded 10/01/2004 by Awyatt
'

'
Application.ScreenUpdating = False

Dim MyFile As String
Dim MyRange As String
Dim MyCloseFile As String


Sheets("MyRange").Select
Range("d17").Select
MyRange = ActiveCell.Value

Sheets("File").Select
Range("B3").Select
MyFile = ActiveCell.Value


Workbooks.Open Filename:=MyFile, UpdateLinks:=3
MyCloseFile = ActiveWorkbook.Name
Sheets("UK Price Retail").Select
Range(MyRange).Select
Selection.Copy

Windows("NewSystem.xls").Activate
Sheets("Costs").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False

Workbooks(MyCloseFile).Activate
Application.CutCopyMode = False
ActiveWorkbook.Close SaveChanges:=False


End Su

 

[RRNZ]

Hi All

Had the same problem that confused me to smitherines and dancing fish's solution gave me a clue to the problem - Windows(MyFile).Close tries to close an object with a LongName of MyFile, however a ShortName reference is needed. This is cured by the following code with no need to declare a new variable: Windows(MyFile.Name).Close SaveChanges:=False

RR from NZ