CODEWITHSUNDEEP
excelvbscript
user5857081
user5857081

Reputation:

Putting a value in a cell using VBS

Anyone knows how to put a value in a cell using VBS? I have this code so far:

option explicit
Dim objWorkbook, wb, ws,logname,user, objExcel,cell

Const xlUp = -4162

Set objWorkbook = CreateObject("Excel.Application")
set user = CreateObject("wscript.network")
logname = user.Username

objWorkbook.Visible = True

Set wb = objWorkbook.Workbooks.Open("C:\test\test.xlsx")
Set ws = wb.Worksheets(1)

With ws
  .Range("A" & .Rows.Count).End(xlUp).Row 'put the value of logname
  .Range("A" & .Rows.Count).End(xlUp).Row 'put the value of date()
  .Range("A" & .Rows.Count).End(xlUp).Row 'put the value of time()
End With

After executing the code, it should increment so that for the next insertion of data, the data to be inserted will be inserted next line with no data. Thanks in advance!

Upvotes: 1

Views: 6811

Answers (1)

Nick Peranzi
Nick Peranzi

Reputation: 1375

Try the below.

option explicit
Dim objWorkbook, wb, ws,logname,user, objExcel,cell

Const xlUp = -4162

Set objWorkbook = CreateObject("Excel.Application")
set user = CreateObject("wscript.network")
logname = user.Username

objWorkbook.Visible = True

Set wb = objWorkbook.Workbooks.Open("C:\test\test.xlsx")
Set ws = wb.Worksheets(1)

With ws
  Set cell = .Range("A" & .Rows.Count).End(xlUp) 'Get last cell in A
  cell.Offset(1,0).Value = logname 'put the value of logname in column A after last row of data
  cell.Offset(1,1).Value = Date 'put the value of date() in column B
  cell.Offset(1,2).Value = Time 'put the value of time() in column C
End With

Upvotes: 1

Related Questions