Closing a Text File

[music_al]

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.

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

 

[xenou]

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