Export to CSV - Excel

[TomElZ]

Hi,

I've currently got a Spreadsheet which uses a Macro to Export to CSV. I have been really lazy and done the code just by recording the macro.

I was just wondering what sort of code I need to allow it to automatically point to a specific directory when the Save As dialog pops up.

Cheers,
Tom

 

[izzop]

Hi,

What code has the Macro generated for you?

If you post it I'll have a look for you.

Regards

 

[dazzerd]

WORKBOOKNAME.saveas YOURDIRECTORY is the basic way to do it.

Just rereading your post of you are simply looking to change the default direcotry that pops up when you press saveas then you can change these in the excel options. No coding needed.

 

[TomElZ]

At the moment I have just got a bog standard:

Sub ExportToCSV()

Dim strFileName As String

strFileName = Application.GetSaveAsFilename(filefilter:="Comma Seperated Values (*.csv), *.csv")

If strFileName <> "False" Then
    ActiveWorkbook.SaveAs strFileName, xlCSV
End If

End Sub

 

[izzop]

Hi,

You can use the InitialFileName Property....

Sub ExportToCSV()

Dim strFileName As String

strFileName = Application.GetSaveAsFilename(InitialFileName:="C:\", filefilter:="Comma Seperated Values (*.csv), *.csv")

If strFileName <> "False" Then
    ActiveWorkbook.SaveAs strFileName, xlCSV
End If

End Sub

I've added it to the code you posted so you can see where to place it, then change the Path to whatever you require.

Regards

 

[TomElZ]

Hi,

Cheers for the reply, that works well! How would I go about giving the file a Default name when the Save As dialogue pops up?

Also, I have some fields I have defined names to which I then move to a separate sheet so I can export to a CSV. So for example C1 is named Test. However, when the row is deleted with that named cell on it, it won't automatically rename C1 to Test. How can I go about setting this so C1 is Test regardless of if the rows are deleted?

 

[TomElZ]

Lovely bit of INDIRECT and taking the named field out solved this problem!