CODEWITHSUNDEEP
excelvbscript
Lou
Lou

Reputation: 403

vbscript update excel spreadsheet

I've looked on many sites, including all the questions that came up when I entered my title, and I can't seem to get my program to work. It activates the spreadsheet, but no data prints.

Option Explicit

Dim objExcel, objWorkbook
Dim strTIN, strName, strFName, strLName, strState, strEmpID, strRecDate, strComment

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\lpeder6\Desktop\Important Info\Data tracking.xlsx")

CopyData

Set objExcel = Nothing
Set objWorkbook = Nothing

'---------------CopyData - Copies required data-----------------

Sub CopyData()

    strTIN = "2-123456789-00005"
    strName = "Smith        John   "
    strState = "MN"
    strEmpID = "S987654321"
    strRecDate = "04/02/2015"
    strComment = "This is all that is in my comment."
    strLName = Trim(Left(strName, 10))
    strFName = Trim(Right(strName, 15))
    strName = strLName & " " & strFName

    objExcel.Visible = True

    objWorkbook.Sheets(1).Activate
    objWorkbook.Sheets(1).Cells(1, 1).Value = strTIN
    objWorkbook.Sheets(1).Cells(1, 2).Value = strName
    objWorkbook.Sheets(1).Cells(1, 3).Value = strState
    objWorkbook.Sheets(1).Cells(1, 4).Value = strEmpID
    objWorkbook.Sheets(1).Cells(1, 5).Value = strRecDate
    objWorkbook.Sheets(1).Cells(1, 6).Value = strComment
    objExcel.ActiveWorkbook.Close

End Sub

Any ideas will be greatly appreciated.

Upvotes: 2

Views: 4458

Answers (1)

user4039065
user4039065

Reputation:

By 'no data prints', I'm assuming that you mean the data you input is not stored. This is because you are not saving the workbook as you close it. Change one line in the sub to:

objExcel.ActiveWorkbook.Close true

See Workbook.Close Method (Excel) for full syntax reference.

Upvotes: 3

Related Questions