Please Help! Excel/Word Macro Not Working...

coxgreen

New Member
Joined
Sep 4, 2015
Messages
2
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.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Where do you have this code? It's usually easier to work with XL VBA to do stuff with Word.... Word VBA is somewhat different and seems harder to use. General outline.... from XL, create a Word application (either using early or late binding) create or open an existing document (or template) and then do stuff. Anyways, maybe describe exactly what you're trying to achieve would be a starting point. Also, please use code tags. HTH. Dave
 
Upvote 0
Where do you have this code? It's usually easier to work with XL VBA to do stuff with Word.... Word VBA is somewhat different and seems harder to use. General outline.... from XL, create a Word application (either using early or late binding) create or open an existing document (or template) and then do stuff. Anyways, maybe describe exactly what you're trying to achieve would be a starting point. Also, please use code tags. HTH. Dave

I initially had the macro in my Excel document, but had trouble getting information from excel into word, that did not include cell formatting from Excel. The word document creates a label, and text from the Excel document was causing the label to go on to multiple labels. I was also seeing multiple errors as the macro executed in Excel, as I would fix one line, I would get a new error. Sorry, I am a newbie. What are/is Code tags, HTH?

Code similar to what you see above, was copying from word, activating the Excel doc, but then gave me object errors as I was telling it to make a cell active to paste into.
 
Upvote 0
I still can't grasp exactly what your outcome objective is? "Label" has a specific VBA meaning and I'm guessing that's not what you're typing about. It seems like you are trying to generate something from XL to put in a Word document. Does the Word doc exist or do you want to create it? If it does exist, where exactly do you want to insert something? When coding U need to know the expected end result before you start and only you know what that is. So please provide as much info as possible and ensure that you answer all of the questions. Please review forum guidelines to learn how to post as code. Dave
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top