Workbook name and extension changes bizarrely after using worksheet.saveas()

faridr

New Member
Joined
Jan 22, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hey everyone!
I have written some VBA code in my workbook called "main.xlsm". In this workbook I have 2 data sheets called POS1 and POS2 that I want to export as TXT. I tried using the worksheet.SaveAs method (will paste the code below). However, a very strange problem occurs when I run the code. The name of the workbook goes from "main.xlsm" to "POS2.txt" (which is actually supposed to be one of the results of my sheet exports, not this whole workbook). For my work, I need the workbook name to stay the same after running my code. Any help would be greatly appreciated!!

VBA Code:
Sub savingTXT()

Dim p1 As Worksheet
Set p1 = ThisWorkbook.Sheets("POS1")

Dim p2 As Worksheet
Set p2 = ThisWorkbook.Sheets("POS1")

p1.SaveAs Filename:="E:\FARID\Excel\new\POS automation\POS1.txt", FileFormat:=-4158, CreateBackup:=False
p2.SaveAs Filename:="E:\FARID\Excel\new\POS automation\POS2.txt", FileFormat:=-4158, CreateBackup:=False

End Sub

EDIT: Just to clarify, I have tried the saveas fileformats 20 and 42 too, and still having the same problem.
 
Last edited by a moderator:

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hello Faridr,
you trying to export sheets instead workbook.
Try to export sheets in this way...
VBA Code:
Sub savingTXT()

     ThisWorkbook.Sheets("POS1").Activate
     ThisWorkbook.SaveAs Filename:="E:\FARID\Excel\new\POS automation\POS1.txt", _
        FileFormat:=-4158, CreateBackup:=False
       
     ThisWorkbook.Sheets("POS2").Activate
     ThisWorkbook.SaveAs Filename:="E:\FARID\Excel\new\POS automation\POS2.txt", _
        FileFormat:=-4158, CreateBackup:=False

End Sub
 
Last edited:
Upvote 0
Hello Farid,
you trying to export sheets instead workbook.
Try to export sheets in this way...
VBA Code:
Sub savingTXT()

     ThisWorkbook.Sheets("POS1").Activate
     ThisWorkbook.SaveAs Filename:="E:\FARID\Excel\new\POS automation\POS1.txt", _
        FileFormat:=-4158, CreateBackup:=False
       
     ThisWorkbook.Sheets("POS2").Activate
     ThisWorkbook.SaveAs Filename:="E:\FARID\Excel\new\POS automation\POS2.txt", _
        FileFormat:=-4158, CreateBackup:=False

End Sub
Hello! Unfortunately, the same problem persists. The workbook still changes its name to "POS2.txt". Really frustrating.
 
Upvote 0
Hi & welcome to MrExcel.
How about
VBA Code:
Sub savingTXT()

     ThisWorkbook.Sheets("POS1").Copy
     ActiveWorkbook.SaveAs Filename:="E:\FARID\Excel\new\POS automation\POS1.txt", _
        FileFormat:=-4158, CreateBackup:=False
     ActiveWorkbook.Close False
    
     ThisWorkbook.Sheets("POS2").Copy
     ThisWorkbook.SaveAs Filename:="E:\FARID\Excel\new\POS automation\POS2.txt", _
        FileFormat:=-4158, CreateBackup:=False
     ActiveWorkbook.Close False
End Sub
 
Upvote 0
Solution
Hi & welcome to MrExcel.
How about
VBA Code:
Sub savingTXT()

     ThisWorkbook.Sheets("POS1").Copy
     ActiveWorkbook.SaveAs Filename:="E:\FARID\Excel\new\POS automation\POS1.txt", _
        FileFormat:=-4158, CreateBackup:=False
     ActiveWorkbook.Close False
   
     ThisWorkbook.Sheets("POS2").Copy
     ThisWorkbook.SaveAs Filename:="E:\FARID\Excel\new\POS automation\POS2.txt", _
        FileFormat:=-4158, CreateBackup:=False
     ActiveWorkbook.Close False
End Sub
Hi Fluff,
Unfortunately your code doesn't solve the problem either. I don't know if my Excel has some malfunction or bug that causes it to act like this, but it really is pretty frustrating.
 
Upvote 0
In what way doesn't it solve the problem?
 
Upvote 0
Oops, forgot to change the POS2 part it should be Activeworkbook.saveas not thisworkbook.
 
Upvote 0
In what way doesn't it solve the problem?
What I did just observe is this: in the saveas, I changed "blabla\POS1.txt" and "blabla\POS2.txt" to "blabla\one.txt" and "blabla\two.txt", respectively. When running the code Excel actually changed the names of some of the sheets to "one" and "two". Why the heck would it do that? We didn't code any sheet name changes. Hope this gives you more insight regarding the problem.
 
Upvote 0
Oops, forgot to change the POS2 part it should be Activeworkbook.saveas not thisworkbook.
Oh man, I cannot thank you enough! Made this change and your code worked. Thank you sooo much!!
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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