0

I have an excel with some sheets. I have a macro that I launch from sheet 1 to export the sheet "INFO" into an CSV file.

I have the macro below, but the original excel is somehow "changed" or it's like after the export I'm viewing the exported CSV.

I want that the macro ends with the exported CSV, without modifying / changing the excel I have open.

Sub Export()

  Dim DefFileName As String
  Dim SaveAsFileName As Variant
  'Static variables keeps there values between the calls
  
  If DefFileName = "" Then
    'The default file name can contain a path:'m 
    DefFileName = Environ("USERPROFILE") & "\EXPORT.CSV"
  End If
  'Ask the user
  SaveAsFileName = Application.GetSaveAsFilename( _
    DefFileName, "CSV (MS-DOS) (*.csv), *.csv")
  If VarType(SaveAsFileName) = vbBoolean Then
    'Aborted
    Exit Sub
  End If
  ThisWorkbook.Sheets("INFO").Activate
  ActiveWorkbook.SaveAs SaveAsFileName, xlCSVUTF8, CreateBackup:=True, Local:=True
End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
andQlimax
  • 778
  • 10
  • 24
  • Can you edit your question, please and show us (pictures...) how the CSV file looks against what it is on the exported sheet? – FaneDuru Jul 23 '20 at 13:24
  • @FaneDuru - no need to. This is a dupe. – BigBen Jul 23 '20 at 13:25
  • @BigBen not exactly what I need, I need to ask the user the file name he wants – andQlimax Jul 23 '20 at 13:26
  • Regardless, the original workbook is being closed (unchanged) and the CSV file is what's open. The problem that the OP thinks is happening is not happening. – John Joseph Jul 23 '20 at 13:27
  • It's *exactly* what you need. You need to create a separate workbook and save that. Asking the user for the filename is not relevant to the problem. – BigBen Jul 23 '20 at 13:27
  • @BigBen: I do not know what a "dupe" wants to be... :) – FaneDuru Jul 23 '20 at 13:27
  • @JohnJoseph In the original workbook I see the name EXPORT.csv and when I close it ask me if I want to save the changes (but it looks like the original workbook, not the exported) – andQlimax Jul 23 '20 at 13:30
  • Ups... Only now I understood his "question"... – FaneDuru Jul 23 '20 at 13:31
  • It looks like the original workbook, because Excel keeps it as it used to be, but it saved only a sheet, the one you activated before SaveAs... Excel wants to be sure that you really want keeping only that sheet (in CSV) or keep all of them, in such a case you must change the file extension. – FaneDuru Jul 23 '20 at 13:33
  • @FaneDuru sorry, I must change the extension of what? my original workbook is like Template.xlsb, the exported is EXPORT.CSV . The exported file is fine. But the original is also is EXPORT.CSV (which is like the original) and it ask me if I want save. How to avoid that? some users receive error 400 also – andQlimax Jul 23 '20 at 13:37
  • **Please** see the duplicate target. Create a new workbook, copy your data into it, and save that workbook as a CSV. – BigBen Jul 23 '20 at 13:39
  • I am afraid you cannot get it... **You do not have to change the extension!** Excel asks you about the saving for the case you saved it as CSV by mistake... **If you made changes in your workbook which has been saved as CSV you will loose them**, in such a case. – FaneDuru Jul 23 '20 at 13:39

0 Answers0