ExcelBanter - Create a new Wb on the Desktop

If you want to create a new workbook on the desktop you not use workbooks.open but must save a file there

Here is a example in VBA that save the activeworkbook to a new folder that it create for you on the desktop

Sub test()
Dim Wsh As Object
Dim FolderPath As String
Dim MyFile As String

Set Wsh = CreateObject("WScript.Shell")
FolderPath = Wsh.SpecialFolders.Item("Desktop")

'Add a slash at the end if the user forget it
If Right(FolderPath, 1) < "\" Then
FolderPath = FolderPath & "\"
End If

'Make folder
On Error Resume Next
MkDir FolderPath & "MyTestFolder" & "\"
On Error GoTo 0

MyFile = "Timesheet Week Ending"

ActiveWorkbook.SaveAs Filename:=FolderPath & "MyTestFolder" & "\" & MyFile & ".xls"
ActiveWorkbook.Close
End Sub

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"Dan R." wrote in message ups.com...
I'm trying to create a new Wb and save it to the desktop... When I run
this, it says the file can't be located... What am I missing?

Sub Test()
Dim csFILENAME As String
Dim wsh As Object
Dim Ws As Worksheet
Dim Wb As Workbook

Set wsh = CreateObject("wscript.shell")
csFILENAME = wsh.SpecialFolders.Item("Desktop")

Set xL = New Excel.Application
Set Wb = xL.Workbooks.Open(csFILENAME & "\" & "Test" & ".xls")

End Sub

Thanks,
-- Dan