SaveAs method changes source file-name

georg7657

New Member
Joined
Jul 30, 2002
Messages
41
Hi experts!
Using the SaveAs method to export a sheet:

Sheets("MySheet").SaveAs _ Filename:="C:Documentsmyexport.csv", _ FileFormat:=xlCSV, CreateBackup:=False

the worksheet name changes to myexport.csv too, so I cant save the original xls file (the source file, I mean).

Can you gimme the trick?
Thanks
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

ROBINSYN

Board Regular
Joined
Aug 19, 2002
Messages
188
Not sure if this works for what you need. But I used this and excel file stayed intact. I'm unsure how to save as other fileformats but mayber you can use this. I got this from posters here and love it. I need my excel file intact because it contain a summary of all PO's created, duplicates and otherwise. Plus I can update vendors, accounts , etc and it is a great source file.

Sheets("Purchase Order").Select
Sheets("Purchase Order").copy
Range("A2:q60").Select
Selection.copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Sheets("Purchase Order").Select

Dim Path As String ' current worksheet path
Dim ThisFileNew As String ' new file name including path
Dim Resp As Integer ' user response to overwrite query
Dim i As Integer ' rename workSHEETS

Target = Range("Q1")'cell contain file name of purchase order, concencated vendorname & PO#
'For new file
Path = ThisWorkbook.Path
If Not Path = "" Then Path = Path & ""
ThisFileNew = Path & Target & " .csv"
Resp = vbOK
' Check for duplicate If Dir(ThisWorkbook.Path & "\" & Target & " .csv") <> "" Then
Resp = MsgBox("You are duplicating files. Are you positive you want to continue ? ", vbExclamation + vbOKCancel)
End If
' Save the workbook if file does not exist, if user wants to overwrite it
If Resp = vbOK Then
ActiveWorkbook.SaveAs Filename:=ThisFileNew
Else
Resp = MsgBox("You will need to rename this file manually and log this overwrite with the accounting department", vbInformation)
End If
ActiveWorkbook.Close'guess your file format name would go here

End Sub
 

Forum statistics

Threads
1,144,048
Messages
5,722,224
Members
422,417
Latest member
Johhny

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