I am new to VB and I am trying to self-learn for a work project (I have been writing JavaScript for the last few years). With both MS Word and MS Excel windows open on the desktop, I am trying to copy data from Word to Excel, defining the Excel cell to start in. No matter what I try, I cannot get past a myriad of errors in the code. Here is my code:
' TimeStamp Macro
'
' Insert Military Timestamp with Alt+Q Executed in the WORD DOCUMENT "ArrivalTime" BookMark
'
Public Sub TimeStamp()
Dim WordApp As Word.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Set XLApp = GetObject(class:="Excel.Application")
XLApp.Visible = True
AppActivate "Microsoft Word"
ActiveDocument.Unprotect ""
Selection.InsertDateTime _
DateTimeFormat:="HH:mm -- MM/dd/yyyy"
ActiveDocument.PrintOut Copies:=1, Collate:=True
ActiveDocument.Bookmarks("PatientName").Range.Copy
'Open and activate Excel workbook.
AppActivate "Microsoft Excel"
'ActiveWorkbook.Activate
'ActiveSheet.Activate
' Goto 1st open/empty cell in this column & activate cell
NextRow = Range("B" & Rows.Count).End(xlUp).Row + 1
ActiveCell.Select
SendKeys "{F2}", True
Selection.Paste
SendKeys "{Tab}", True
AppActivate "Microsoft Word"
ActiveDocument.Bookmarks("Complaint").Range.Copy
AppActivate "Microsoft Excel"
SendKeys "{F2}", True
Selection.Paste
SendKeys "{Tab}", True
ActiveCell.Value = Now()
ActiveCell.NumberFormat = "HH:mm"
'This should move to the next cell on the next line
SendKeys "{Enter}", True
ActiveWorkbook.Save
'Reset Word document for next input
AppActivate "Microsoft Word"
ActiveDocument.Characters.First.Select
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=False
End Sub
Any help would be greatly appreciated. I have spent 40+ hours on this so far. Thanks for your expertise.
' TimeStamp Macro
'
' Insert Military Timestamp with Alt+Q Executed in the WORD DOCUMENT "ArrivalTime" BookMark
'
Public Sub TimeStamp()
Dim WordApp As Word.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Set XLApp = GetObject(class:="Excel.Application")
XLApp.Visible = True
AppActivate "Microsoft Word"
ActiveDocument.Unprotect ""
Selection.InsertDateTime _
DateTimeFormat:="HH:mm -- MM/dd/yyyy"
ActiveDocument.PrintOut Copies:=1, Collate:=True
ActiveDocument.Bookmarks("PatientName").Range.Copy
'Open and activate Excel workbook.
AppActivate "Microsoft Excel"
'ActiveWorkbook.Activate
'ActiveSheet.Activate
' Goto 1st open/empty cell in this column & activate cell
NextRow = Range("B" & Rows.Count).End(xlUp).Row + 1
ActiveCell.Select
SendKeys "{F2}", True
Selection.Paste
SendKeys "{Tab}", True
AppActivate "Microsoft Word"
ActiveDocument.Bookmarks("Complaint").Range.Copy
AppActivate "Microsoft Excel"
SendKeys "{F2}", True
Selection.Paste
SendKeys "{Tab}", True
ActiveCell.Value = Now()
ActiveCell.NumberFormat = "HH:mm"
'This should move to the next cell on the next line
SendKeys "{Enter}", True
ActiveWorkbook.Save
'Reset Word document for next input
AppActivate "Microsoft Word"
ActiveDocument.Characters.First.Select
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=False
End Sub
Any help would be greatly appreciated. I have spent 40+ hours on this so far. Thanks for your expertise.