Desktop Saving in VB

  • Thread starter Thread starter BOHICA
  • Start date Start date
B

BOHICA

Hello...I have created a macro that moves data from a large
spreadsheet over to a new book that is much smaller size-wise for
e-mailing so it doesn't spank the e-mail server sooo hard. (original
spreadsheet is about 6MB, data needed is about 200KB). Several people
on several workstations (Windows2000) would run this macro on their
own spreadsheets for their area. My question is this: is there a way
to save this new spreadsheet using the save as commands where no
matter what machine name its on it goes to desktop for easy access?
(that way i do not have to specify the c:\Documents and
Settings\SillyNameOX25\Desktop where 'SillyNameOX25' is different
for each computer and login name)

I have inserted the code I have already


Sheets("Pumps").Move
ActiveWorkbook.SaveAs Filename:= _
DForm.Dsave & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

DForm.Dsave is a pre-determined file name that is configured in a
form...FYI

I have tried an actual path, and when I do it like the way above, it
automatically saves it in the My Documents folder...wassup wit dat?
Any way to put it on the desktop?

Thanks
 
This should return the location of the desktop. I use it in VBScript, I don't know what modifications (if any) you would need to make it work in Excel

Set WshShell = WScript.CreateObject("WScript.Shell"
strDesktop = WshShell.SpecialFolders("Desktop")
 
Back
Top