mkraemer11
asked on
VBS script - Need to change location of output file
The following script places the outputfile on the desktop. I need it to be placed in "C:\FaxConfirmation". Can anyone tell me what I need to add in the coding and where?
' Specify the template xls file that you wish to update here
strTemplateFile = Replace(WScript.ScriptFull Name, WScript.ScriptName, "") & "FaxConfirmation.xls"
Set objExcel = CreateObject("Excel.Applic ation")
Const xlUp = -4162
Const xlDown = -4121
Const xlAnd = 1
objExcel.Visible = True
'First open the template workbook
objExcel.Workbooks.Open strTemplateFile
Set objTemplateWB = objExcel.ActiveWorkbook
Set objDialog = CreateObject("UserAccounts .CommonDia log")
objDialog.Filter = "CSV Files (*.csv)|*.csv;All Files (*.*)|*.*"
objDialog.FilterIndex = 1
objDialog.InitialDir = "."
intResult = objDialog.ShowOpen
If intResult = 0 Then
MsgBox "No file was selected."
objExcel.Quit
Else
objExcel.Workbooks.Open objDialog.FileName
Set objCSVWB = objExcel.ActiveWorkbook
objCSVWB.Sheets(1).UsedRan ge.Copy objTemplateWB.Sheets(1).Ce lls(65536, "A").End(xlUp).Offset(1, 0)
objCSVWB.Activate
objExcel.ActiveWorkbook.Cl ose False
objTemplateWB.Activate
objExcel.ActiveWorkbook.Sa veAs objExcel.ActiveWorkbook.Pa th & "\" & Replace(objExcel.ActiveWor kbook.Name , ".xls", "") & "_" & FormatDateToString(Now) & ".xls"
objExcel.ActiveWorkbook.Cl ose False
objExcel.Quit
End If
Function FormatDateToString(dDateTi me)
FormatDateToString = Year(dDateTime) & "-" &_
Right("00" & Month(dDateTime), 2) & "-" &_
Right("00" & Day(dDateTime), 2) & "-" &_
Right("00" & Hour(dDateTime), 2) & "-" &_
Right("00" & Minute(dDateTime), 2) & "-" &_
Right("00" & Second(dDateTime), 2)
End Function
' Specify the template xls file that you wish to update here
strTemplateFile = Replace(WScript.ScriptFull
Set objExcel = CreateObject("Excel.Applic
Const xlUp = -4162
Const xlDown = -4121
Const xlAnd = 1
objExcel.Visible = True
'First open the template workbook
objExcel.Workbooks.Open strTemplateFile
Set objTemplateWB = objExcel.ActiveWorkbook
Set objDialog = CreateObject("UserAccounts
objDialog.Filter = "CSV Files (*.csv)|*.csv;All Files (*.*)|*.*"
objDialog.FilterIndex = 1
objDialog.InitialDir = "."
intResult = objDialog.ShowOpen
If intResult = 0 Then
MsgBox "No file was selected."
objExcel.Quit
Else
objExcel.Workbooks.Open objDialog.FileName
Set objCSVWB = objExcel.ActiveWorkbook
objCSVWB.Sheets(1).UsedRan
objCSVWB.Activate
objExcel.ActiveWorkbook.Cl
objTemplateWB.Activate
objExcel.ActiveWorkbook.Sa
objExcel.ActiveWorkbook.Cl
objExcel.Quit
End If
Function FormatDateToString(dDateTi
FormatDateToString = Year(dDateTime) & "-" &_
Right("00" & Month(dDateTime), 2) & "-" &_
Right("00" & Day(dDateTime), 2) & "-" &_
Right("00" & Hour(dDateTime), 2) & "-" &_
Right("00" & Minute(dDateTime), 2) & "-" &_
Right("00" & Second(dDateTime), 2)
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The error is telling you the script cannot find the specified file.
Line 12 is objExcel.Workbooks.Open strTemplateFile
strTemplate file is FaxConfirmation.xls located in the same folder where the script itself is located.
You need to have an Excel template file named FaxConfirmation.xls in the same folder as the script.
If you want to do this from the c:\FaxConfirmation folder then simply run the script from that folder. If so, the original script should do what you want without the earlier change.
OM Gang
Line 12 is objExcel.Workbooks.Open strTemplateFile
strTemplate file is FaxConfirmation.xls located in the same folder where the script itself is located.
You need to have an Excel template file named FaxConfirmation.xls in the same folder as the script.
If you want to do this from the c:\FaxConfirmation folder then simply run the script from that folder. If so, the original script should do what you want without the earlier change.
OM Gang
ASKER
' Specify the template xls file that you wish to update here
strTemplateFile = Replace(WScript.ScriptFull
Set objExcel = CreateObject("Excel.Applic
Const xlUp = -4162
Const xlDown = -4121
Const xlAnd = 1
objExcel.Visible = True
'First open the template workbook
objExcel.Workbooks.Open strTemplateFile
Set objTemplateWB = objExcel.ActiveWorkbook
Set objDialog = CreateObject("UserAccounts
objDialog.Filter = "CSV Files (*.csv)|*.csv;All Files (*.*)|*.*"
objDialog.FilterIndex = 1
objDialog.InitialDir = "."
intResult = objDialog.ShowOpen
If intResult = 0 Then
MsgBox "No file was selected."
objExcel.Quit
Else
objExcel.Workbooks.Open objDialog.FileName
Set objCSVWB = objExcel.ActiveWorkbook
objCSVWB.Sheets(1).UsedRan
objCSVWB.Activate
objExcel.ActiveWorkbook.Cl
objTemplateWB.Activate
objExcel.ActiveWorkbook.Sa
objExcel.ActiveWorkbook.Cl
objExcel.Quit
End If
Function FormatDateToString(dDateTi
FormatDateToString = Year(dDateTime) & "-" &_
Right("00" & Month(dDateTime), 2) & "-" &_
Right("00" & Day(dDateTime), 2) & "-" &_
Right("00" & Hour(dDateTime), 2) & "-" &_
Right("00" & Minute(dDateTime), 2) & "-" &_
Right("00" & Second(dDateTime), 2)
End Function
VBS-error.doc