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:

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
382
Office Version
  1. 2007
Platform
  1. Windows
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:

faridr

New Member
Joined
Jan 22, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

faridr

New Member
Joined
Jan 22, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
In what way doesn't it solve the problem?
 

faridr

New Member
Joined
Jan 22, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In what way doesn't it solve the problem?
The workbook's name still automatically changes to"POS2.txt" after running the code.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
Oops, forgot to change the POS2 part it should be Activeworkbook.saveas not thisworkbook.
 

faridr

New Member
Joined
Jan 22, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.
 

faridr

New Member
Joined
Jan 22, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,318
Messages
5,623,972
Members
416,002
Latest member
Neshx

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
Top