Close Save As Workbook

ChrisGrigg

New Member
Joined
Nov 14, 2018
Messages
6
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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.

Code:
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
 
Upvote 0
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
 
Upvote 0
Try this

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top