Close Save As Workbook

[ChrisGrigg]

Hi all

Hoping for some help on the below please.

I have an xlsm workbook (Work Order Listing) that I want to save as as a unicode txt file (Cheops Upload) without prompts. The VBA code is in the Work Order listing workbook.

I can achieve this but with the below but is closes Work Order Listing. I need Work Order Listing to remain open and have Cheops Upload to close and save without prompts.

Sub Save_As_Text()
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\Users\cgrigg\Documents\Cheops Upload.txt", FileFormat:=xlText
Application.DisplayAlerts = True
End Sub

I hope i have explained this ok?? Any help is appreciated

Thanks Chris

 

[Coding4Fun]

As you are saving the Active workbook as something else it is not actually closing the original file but replacing it as the new file since you are saving it as a new file. An option would be to reopen the Work Order Listing again after saving it as a new file.

Sub Save_As_Text()


Application.DisplayAlerts = False
On Error GoTo myError
ActiveWorkbook.SaveAs Filename:="C:\Users\cgrigg\Documents\Cheops Upload.txt", FileFormat:=xlText
Workbooks.Open Filename:="C:\Users\cgrigg\Documents\Work Order Listing.xlsm"
Application.DisplayAlerts = True
Exit Sub
''Errors out if the file is open
myError:
If Err.Number = 1004 Then
MsgBox "Looks like the file my be open or in use, close the file and try again. C:\Users\cgrigg\Documents\Cheops Upload.txt"
End If
End Sub

 

[ChrisGrigg]

Thanks for that. I entered the code as suggested but i just get the error message you created and it does not reopen the original xlsm file.

The flow process of what I am trying to achieve is as follows;

1. xlsm file updates its data every half an hour from SQL server (that's why I need it to stay open)
2. xlsm file saves as txt file every half an hour
3. the txt file is then used to import data into another system every half an hour

Apologies if I am not making sense. Please let me know if you need any other info.

Chris

 

[ChrisGrigg]

Looking for a solution that does not require any user input as it will sit running on a remote computer.

 

[Coding4Fun]

Try this

Sub Save_As_Text()


Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\Users\cgrigg\Documents\Cheops Upload.txt", FileFormat:=xlText
Workbooks.Open Filename:="C:\Users\cgrigg\Documents\Work Order Listing.xlsm"
Workbooks("Cheops Upload.txt").Close
Application.DisplayAlerts = True
Exit Sub


End Sub