Hi,

I track weekly timesheets on an excel spreadsheet and use VBA to create sheets from userforms, merge sheets to workbooks, and email to main office. The PC I was using stored files locally on hard drive and all was well for a few years. I have now been assigned a terminal server and the sheets/workbooks are now located on a network drive.

I edited the modules to reflect the destinations but have troubles getting the files to save and merge from this network location. I find that the files do not save to the network drive but I find them in the default dir (My Documents) BTW I do have read/write permissions.

I have attached a bit of code where I seem to have the trouble. Please help!

Sub Save_Sheet()
'Save time sheet with job name and number in filename
Dim x
Dim wb As Workbook
Dim todate As String
Dim Foldername As String
jname = Range("B3")
jnumber = Range("H3")
todate = Format(Now, "mm-dd-yy")
Foldername = ("\\webserver\e\Time\" & todate)
Workbookname = ("\\webserver\e\Time\" & todate & ".xls")
Application.ScreenUpdating = False
On Error Resume Next
MkDir Foldername
On Error GoTo 0
ChDir (Foldername)
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs jname & " " & jnumber & ".xls", Password:="test"
End With
ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub

The result from the code above places my timesheets in the default (My Documents) folder. I then copy the files from My Documents to the folder on the webserver machine, e: drive, Time folder.

Then I attempt to merge them with th following code:

Sub sheetmrg()
'To merge weekly time sheets to one book for emailing
Dim sBook As Workbook
Dim tbook As Workbook
Dim aSht As Worksheet
Dim Foldername As String
Dim todate As String
Application.ScreenUpdating = False
Set tbook = Workbooks.Add
todate = Format(Now, "mm-dd-yy")
Foldername = ("\\webserver\e\Time\" & todate & "\")
fName = Dir(Foldername)
Do While (fName <> "")
ChDir (Foldername)
Set sBook = Workbooks.Open(fName, , , , "xxxx") ' sheets pass protected
For bb = 1 To sBook.Sheets.Count
Set aSht = sBook.Sheets(bb)
aSht.Copy after:=tbook.Sheets(tbook.Sheets.Count)
tbook.Sheets(tbook.Sheets.Count).Name = Range("H3")
Next bb
fName = Dir
sBook.Save
sBook.Close
ProgressDlg2.Show
Loop
Set xlBook = Nothing
Set xlSht = Nothing
Set tbook = Nothing
Set tbook = ActiveWorkbook
ActiveWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
tbook.SaveAs ("\\webserver\e\Time\" & todate & ".xls")
tbook.Close
Application.ScreenUpdating = False
MsgBox "Merge Complete!"
UserForm5.Show

End Sub

Here I get a runtime error 1004 cannot find "timesheet 10004.xls" when the sheet IS there. I discovered after numerous tries that a copy needs to be in My Documents as well for the merge to work. The tbook does save in the correct location.

I am stumped....when I change all locations to the local drive on my terminal server all works fine. Am I missing something??

Thanks so much for any help!!