Create a new Wb on the Desktop

[Dan R.]

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

 

[Ron de Bruin]

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