CODEWITHSUNDEEP
excelfunctionvbscript
Dan Brookwell
Dan Brookwell

Reputation: 341

Unknown Runtime Error VBScript Excel

I am trying to oepn an excel file, write a variable from a user input into cell D4, then read cell E4. I keep getting ann Unknown Runtime Error

Sub ReportTP()

Dim objExcel, wb, ws

TPdate = document.getElementById("TPreportDate").value

Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Visible = True

Set objWorkbook = objExcel.Workbooks.Open("path to file")
objExcel.ActiveSheet.Unprotect "SFGA"

Set objWorksheet = objWorkbook.Worksheets(1)

objExcel.Cells(D4).value = TPDate

reportVarTP = objExcel.Cells(E4).value

msgbox reportVarTP
objExcel.ActiveSheet.Protect
objExcel.ActiveWorkbook.Save 
objExcel.ActiveWorkbook.Close

objExcel.Application.Quit

End Sub

Upvotes: 0

Views: 6083

Answers (1)

user4039065
user4039065

Reputation:

You are trying to set cell values from the application, not the worksheet.

Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Cells(D4).value = TPDate
reportVarTP = objWorksheet.Cells(E4).value

Use the parent Worksheet object to identify the cells.

objExcel.ActiveSheet.Unprotect "SFGA"

This does not define the worksheet properly. Excel typically opens to whatever worksheet was active when the workbook was last shut down. Use objWorksheet or some other more definitive method of defining the worksheet to be unprotected (e.g. Worksheet .Name property).

Upvotes: 1

Related Questions