Here is my code so far. I am returning an error on Selection.HomeKey (wdStory) w/ "Object variable or with block variable not set"
Help would be greatly appreciated! I believe I am almost there it successfully opens the file and copies the first cell I want 2,2 but then errors out.
Please Login or Register to view this content.
As if it cannot navigate through to Word, it copies the selected text but has trouble transitioning.
The dividers for the information are : 'Business Names , 'Lots , 'End date invoice paid through , and 'Amount Received
These are the 4 different values I will be taking from Excel spreadsheet.
Sorry re-typo.
Last edited by DadaaP; 05-30-2013 at 03:52 PM.
Any ideas please?
It's almost impossible to work out what your code is doing, since you've not posted the document and you have loads of entirely unnecessary selection moves, homekeys etc. Furthermore, you've got some strange goings-on in your code. If you're trying to transfer something to Word, why do you have 'Set wdApp = CreateObject("Excel.application")'? A wdApp declaration would normally reference a Word object, but you have it referencing an Excel one. Consistent with that, you have 'Set wdDoc = wdApp.Workbooks.Open', but then you never reference wdDoc except to switch focus.
Without knowing quite what you're doing, it nevertheless appears to me your code could be reduced to something like:
The above assumes all your 'MoveDown' counts refer to paragraphs and the 'MoveRight' expressions are finding the ends of those paragraphs. Probably not right but, as I said, I don't know what your document's structure is. The code could be made even better if you actually used bookmarks in the Word document for the output destinations ...Please Login or Register to view this content.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
If they were Bookmarks would you have to define each bookmark first?
If so would you doAnd so on and so forth for every bookmark?Please Login or Register to view this content.
Then how to do you run your insert feature, which by the way is way....way....way better than I knew how to do. Something as simple as a bookmark saves a shiz load of time. Now how to insert at that bookmark?
By the way thank you very much for the help it is extremely helpful
If you don't define them before running the macro, there's no point in trying to use bookmarks. Simply assign each bookmark (there's only four) to the Word ranges you want the data to go to. From then on, it's quite a simple matter to have the macro update them. For example:
Although the code's a bit more complicated, it's also more robust (error-checking and the same bookmarks can be re-used later on) and now also terminates your Excel session.Please Login or Register to view this content.
With wdDoc
ActiveDocument.Bookmarks("A").Range.InsertAfter xlWkSht.Cells(2, 2).Text
ActiveDocument.Bookmarks("B").Range.InsertAfter xlWkSht.Cells(2, 3).Text
ActiveDocument.Bookmarks("C").Range.InsertAfter xlWkSht.Cells(2, 4).Text
ActiveDocument.Bookmarks("D").Range.InsertAfter xlWkSht.Cells(2, 5).Text
End With
And that would explain why it only could reference the first... Thank you
Set xlWkSht = Nothing: Set xlWkBk = Nothing: Set xlApp = Nothing: Set wdDoc = Nothing
Why do this? Is it proper to after using the variables to set them to nothing.
With objects, it's always a good idea to clear them once you're finished with them. It guarantees their memory will be released for other uses by your system.Originally Posted by DadaaP
For some reason the code is not entering anything however does not debug. Im sure its a simple fix that after screwing around I should be able to find.
Macropod you seriously deserve a tip for this information! Its been I guess a long two years since taking classes in VBA, its a slow recovery process
Are you absolutely sure there's anything in the referenced cells (B2, C2, D2, E2) on "Sheet1" in "C:\Users\David\Desktop\PRG Files\Values.xlsx"?
Are you also sure the code is being run from the document that contains the bookmarks? As per your original, the code refers to 'ThisDocument', not to 'ActiveDocument'.
Last edited by macropod; 05-31-2013 at 12:49 AM.
100% sure on both questions. There are values in the cells and the bookmarks and macro are being run from same word file.
If you can attach to a post a document with your bookmarks & code and a workbook with the sample data, I'll take a look at them.
IF I set xlApp.Visible = True as True as shown instead of False I can watch it open the excel file and close suddenly without inputs.
Here are the two files
Disregard the fact its checking a file named CheckRun its the same thing as Values.xlsx
Your data are NOT in B2, C2, D2, E2 - they're in B2, B3, B4, B5!!!As if that's not enough, the macro isn't in your Word document.
This is all fairly basic stuffThe code will work fine with those issues addressed.
CheckRun?
Last edited by macropod; 05-31-2013 at 01:17 AM.
Drr! So the file was given to me and should of known that I was creating the macro under the template directory. Recreated it now it works flawlessly. Inside the loop it goes and will print 35 pages that now saves me 3 hours every week
Thank you macropod! Now let me tip you!
You can do that by clicking on the * in the bottom left corner of one of my posts to update my reputation. Please also mark the thread as Solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks