Andrew Hillas
asked on
Saving excel document in lotus script
Hello,
I am trying to save to an excel file, information from a view in lotus notes. I have already created the excel template and just place the information I want in the correct cells. All this works great, the information is collected, the excel template opened and all data passed over. The problem is when trying to save the excel file. I want to save it to a named folder with a new name. If I try to create a variable with the full path name, then the file is not saved at all
The xlApp and xlWorkbook have been passed over from the call out sub. xlFileName is a public declared variable
If I try with just a file name e.g "Filename that works" then the excel workbook is saved with the new name BUT it is in the workbooks default file path folder, so this doesn't work:
Please help
I am trying to save to an excel file, information from a view in lotus notes. I have already created the excel template and just place the information I want in the correct cells. All this works great, the information is collected, the excel template opened and all data passed over. The problem is when trying to save the excel file. I want to save it to a named folder with a new name. If I try to create a variable with the full path name, then the file is not saved at all
The xlApp and xlWorkbook have been passed over from the call out sub. xlFileName is a public declared variable
Sub SaveLogFile (xlApp As Variant, xlWorkbook As Variant)
Dim FolderName As String, SaveName As String
Dim PssWrd As String
Dim i As Integer
FolderName = "C:/Lotus/Notes/Overtime Logs/" + CStr(Year(Today))
SaveName = FolderName + "/" + xlFileName
PssWrd = "mypassword"
REM Check/create folder to save the log file to
On error Resume Next
MkDir(FolderName)
For i = 1 To xlWorkbook.Worksheets.Count
With xlWorkbook.Worksheets(i)
.Protect PssWrd
End With
Next i
xlApp.ActiveWorkbook.SaveAs SaveName
xlApp.ScreenUpdating = True
xlApp.Visible = True
Set xlApp = Nothing
End Sub
All works fine, until saving. The workbook becomes visible, but has not been saved, with it's original name.If I try with just a file name e.g "Filename that works" then the excel workbook is saved with the new name BUT it is in the workbooks default file path folder, so this doesn't work:
xlApp.ActiveWorkbook.SaveAs SaveName
but this does:xlApp.ActiveWorkbook.SaveAs (xlFileName)
I have looked and looked at forums and can not see any solution.Please help
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm thinking rights and permissions... Is the folder created if it didn't exist prior to the MkDir call? Usually, the Notes directory is made read-only by lots of Admins.
ASKER
That was my mistake!! So simple and yet so frustrating. Thank you very much
Welcome, glad that was useful.
~bp
~bp