Need VBA To Save Current XLS File as CSV to Desktop

[shawthingz]

Hi All,

Hope someone can help - I've not managed to find anything on any message boards so far...

At the end of some other VBA processing (which I've managed to get working), I'm looking for some code that saves the current active worksheet of the current open file to the user's Desktop as a CSV file using the same filename (without any dialogue boxes popping up) & then closes the file.

e.g. Ctive Sheet 3 of "ATestFile.xls" would be saved as Desktop/"ATestFile.csv"

The macro needs to work for 2003, 2007 & 2010 & on the Vista / Win2007 Operating Systems.

Hope this is possible & makes sense.

Thx

 

[shawthingz]

Hi (Again) All,

Not had any replies to this request yet, but FYI I'm now posting my own reply to my original posting as (after ~6 additional hours of wading through various postings on t'inter-web) I think I've finally managed to solve the problem I originally asked for assitance with.

Here's the code I ended up with in the end...

TBH, only about 5% of is my own 'work', the rest has been "pulled together" from postings I've managed to find in various forums / message boards (including this one), so a BIG THANK YOU for all the assistance provided by all previous respondees to the questions that get posted - hope I'm (finally) helping to give a little back to this community!

---


Sub SaveToUsersDesktopAsCSVandAutoCloseFileWithNoWarnings()

Dim DTAddress As String
Dim FileName As String
Dim FullyQualifiedFileName As String

'Get the path to the User's Desktop
DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator

'Build the Workbook name to save as CSV format File
FileName = ActiveWorkbook.Name
FileName = Left(FileName, InStr(FileName, ".")) & "csv"

'Build the fully-qualified Workbook "save" path
FullyQualifiedFileName = DTAddress & FileName

'Switch Off all Application alerts before saving (in case the file already exists etc.)
Application.DisplayAlerts = False

'Save the current active sheet as a CSV file to the user's Desktop
ActiveWorkbook.SaveAs FileName:=FullyQualifiedFileName, FileFormat:=xlCSV

'Pop-up an info dialogue box to confirm where the file has been saved to
Answer = MsgBox(FileName & Chr(13) & Chr(13) & "Click OK To Close This Workbook", _
vbOKOnly + vbInformation, "This File Has Been Saved to Your Desktop")

'Close the original Workbook without saving changes
ThisWorkbook.Close savechanges:=False

'Switch all Application alerts back on before exiting
Application.DisplayAlerts = True

End Sub

---

This probably isn't the most 'elegant' VBA solution but it seems to work - hope it helps someone looking for something similar in the future

shawthingz

 

[DaHenk]

I just had to create an account to say Thank You! You summed up pretty much everything i needed for the file-saving I was attempting.

I really appreciate how you actually posted the results you got from your research so that others (like me) could use it, instead of just using it and then forgetting it.

I googled for "save current user desktop excel vba" and this was the second hit =)

 

[cgeorge4]

I am thanking you also.

I needed this exact function.....and I can't wait to use it tomorrow morning at work.

Good Job!

 

[shawthingz]

Thanks DaHenk & cgeorge4 for your kind responses, they're much appreciated!

As a side-note, I'm also now very proud that I'm now (finally) "famous" on t'inter-web
>> I googled for "save current user desktop excel vba" and this was the second hit

TBH, I'm self-taught, still having fun learning & (very probably) a bit of a "hacker" as I'm sure this isn't the 'cleanest' way to do this, but I figure that if it works, "ship it"

As a follow-up from me, the people who are now using this new workbook in various depts across the company I work for (as part of their new Business Process) have all said that they absolutely love the "magic" that now happens when they just click on the button!

 

[somenoob]

Sub SaveToUsersDesktopAsCSVandAutoCloseFileWithNoWarnings()

Dim DTAddress As String
Dim FileName As String
Dim FullyQualifiedFileName As String

'Get the path to the User's Desktop
DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator

'Build the Workbook name to save as CSV format File
FileName = ActiveWorkbook.Name
FileName = Left(FileName, InStr(FileName, ".")) & "csv"

'Build the fully-qualified Workbook "save" path
FullyQualifiedFileName = DTAddress & FileName

'Switch Off all Application alerts before saving (in case the file already exists etc.)
Application.DisplayAlerts = False

'Save the current active sheet as a CSV file to the user's Desktop
ActiveWorkbook.SaveAs FileName:=FullyQualifiedFileName, FileFormat:=xlCSV

'Pop-up an info dialogue box to confirm where the file has been saved to
Answer = MsgBox(FileName & Chr(13) & Chr(13) & "Click OK To Close This Workbook", _
vbOKOnly + vbInformation, "This File Has Been Saved to Your Desktop")

'Close the original Workbook without saving changes
ThisWorkbook.Close savechanges:=False

'Switch all Application alerts back on before exiting
Application.DisplayAlerts = True

End Sub

Hi, i need some help. i need a code similar to this. but

i don't want to close the excel after i run the macro.

i need the csv file to be named the 'workbookname + activesheet name.csv'

after running the macro, i need the csv file to be saved at the desktop. and the current excel i am viewing, remains untouched. problem now is that after running the code you have, the excel i am viewing, the activesheet name changes.
i need the excel to remain as it is. and have a csv file in the desktop.
the excel and csv are seperate files. the excel remain as it is. and the csv file contains data from the activesheet i am viewing.

please help. Thanks alot!

 

[cgeorge4]

Hi, I'm not at my computer right now - but if u go to my profile to see all threads STARTED by me, look for one regarding CSV, and scroll down and read thru it - I think u will find a code that will do what u want.

I'm very detailed - so just read my responses.

Letme know if that helps

Juicy

 

[lebinho]

Hell iI ned eactly the same eatues a smnoob. Coulanyon help m?

Hi, i need some help. i need a code similar to this. but

i don't want to close the excel after i run the macro.

i need the csv file to be named the 'workbookname + activesheet name.csv'

after running the macro, i need the csv file to be saved at the desktop. and the current excel i am viewing, remains untouched. problem now is that after running the code you have, the excel i am viewing, the activesheet name changes.
i need the excel to remain as it is. and have a csv file in the desktop.
the excel and csv are seperate files. the excel remain as it is. and the csv file contains data from the activesheet i am viewing.

please help. Thanks alot!

 

[rhybeka]

Thanks a bunch! just had to modify it a bit for my needs works a treat!