Closing a Text File

music_al

Board Regular
Joined
Nov 26, 2008
Messages
131
Hi

We have a system that creates a file with a non-standard extension - filename.0000. So the filename might be NRExport.4567. The next file to be exported would be NRExport.4568 and so on.

I open this file as a comma delimited file and perform some cleansing on the file using code. I then save the file as a text file. This all works fine until I want to close the file. I get a 'Subscript out of range' error on the line highlighted.

Code:
Sub SaveAs_ARIS_File()


    Dim Original_Filename As String, New_Filename As String
    Original_Filename = Worksheets("Sheet1").Range("A1")
    Application.DisplayAlerts = False    'Turn off display alerts
    
    Sheets(2).Select
    Sheets(2).Move
    ActiveWorkbook.SaveAs Filename:=Original_Filename & ".txt", FileFormat:=xlText, CreateBackup:=False
    
    New_Filename = Original_Filename & ".txt"
    
    'I get an error on this line - Subscript out of range
[COLOR=#ff0000]    Workbooks(New_Filename).Close False[/COLOR]
    
    
    Application.DisplayAlerts = True    'Turn on display alerts
    
End Sub
 
Last edited:

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,586
Office Version
2013
Platform
Windows
Hi,
What are the values of New_FileName and Original_FileName when you encounter this error? Which workbook is active when you encounter this error?

The error is telling you the workbook isn't open so either you are using the name incorrectly or the workbook is already closed (or both) - basically, even with these very few lines of code it is hard to follow without knowing your expectation of what workbook is active and what is happening with these saves. However, as a rule, if you move a sheet then that sheet becomes the new active workbook.

A suggestion would be to set and use workbook references rather than relying on string names.

Also wouldn't call a variable "new filename" if it has the value of original filename - that's just confusing. Why is it a new filename when it doesn't have a new filename?


Debugging tools:
http://krgreenlee.blogspot.com/2006/04/programming-excel-vba-debugging-for.html
 
Last edited:

Forum statistics

Threads
1,085,844
Messages
5,386,303
Members
401,992
Latest member
CleverHopper

Some videos you may like

This Week's Hot Topics

Top