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

[faridr]

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!!

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.

 

[EXCEL MAX]

Hello Faridr,
you trying to export sheets instead workbook.
Try to export sheets in this way...

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

 

[faridr]

Hello Farid,
you trying to export sheets instead workbook.
Try to export sheets in this way...

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]

Hi & welcome to MrExcel.
How about

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

 

[faridr]

Hi & welcome to MrExcel.
How about

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]

In what way doesn't it solve the problem?

 

[faridr]

In what way doesn't it solve the problem?

The workbook's name still automatically changes to"POS2.txt" after running the code.

 

[Fluff]

Oops, forgot to change the POS2 part it should be Activeworkbook.saveas not thisworkbook.

 

[faridr]

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]

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!!