I am working with Excel 2007.
I have a spreadsheet I send out to multiple people. They fill it out and then click a submit button. The code automatically saves the workbook to their desktop and then emails the workbook back to me.
The problem I'm having is the directory path for the desktop for Windows XP and Windows 7 is different. (Ex. for XP it's "C:\Users\UserName\Desktop") I already figured out how to find the computer's username.
Is there a way to use VBA to determine which operating system is being used and then save the workbook using the appropriate directory path?
Any ideas? Thanks!
Worked in Windows 7 64 bit using Excel 2010.
this should retrieve the desktop path. I only have vista at this location, but I would expect it to work in XP and Windows 7 as well.
Dim wsh As Object
Dim DesktopPath As String
Set wsh = CreateObject("wscript.shell")
DesktopPath = wsh.SpecialFolders.Item("Desktop")
s = Right(DesktopPath, 1)
If s <> "\" Then DesktopPath = DesktopPath & "\"
You can integrate that code into your current code.
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here