PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
How to Develop Macro on Excel which opens a word document and feed data into table-VBForums
Results 1 to 15 of 15

Thread: How to Develop Macro on Excel which opens a word document and feed data into table

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2008
    Posts
    2

    How to Develop Macro on Excel which opens a word document and feed data into table

    Please help me in Developing a macro on an excel workbook which should open a word document and feed data into the respective columns within a table located within word document.

    Requirement::
    Excel::
    1. Excel contains data on different cells A1, B1, C1 and so on

    Word::
    1. Word document contains a table resembling a form conatining 20+ fields where the data from the excel should be entered.

    Develop macro which should open a word document from a particular location on the local drive(for example "D" drive) and feed the data present on different cells of the excel spreadsheet to the specific fields on the word document.

  2. #2
    Fanatic Member dmaruca's Avatar
    Join Date
    May 2006
    Location
    Jacksonville, FL
    Posts
    566

    Re: How to Develop Macro on Excel which opens a word document and feed data into table

    Do you know VBA?

  3. #3

    Thread Starter
    New Member
    Join Date
    Jul 2008
    Posts
    2

    Re: How to Develop Macro on Excel which opens a word document and feed data into table

    Just know it is visual basic for applications... not much about it.

  4. #4
    Member
    Join Date
    May 2008
    Posts
    36

    Re: How to Develop Macro on Excel which opens a word document and feed data into table

    I'm going (and have gone...) through the same process, Mithun. I've spent the past six months building Excel models and Word templates, and now I'm "pulling it all together".

    Feel free to PM me, but I'll give you a quick run-down.


    First off, in Word, go to Tools -> Options -> View and turn "Field Shading" pull-down box to "Always". That'll "show" you what's coming in from Excel.

    Second, make sure that you open Excel, then Word, ALWAYS. Reversing it will open Excel as a background process, and it'll drive you nuts...

    OK...

    In Excel, you want to 'name' each cell that you want to bring over into Word.
    ----How? Where it normally says "A1" or "B10", type in a descriptive name for the cell - more importantly - something that won't be duplicated and that isn't overly simplified. Instead of the generic "name", put "MyName" so that it doesn't get confused with MY name, MY WIFE'S name, MY CAT'S NAME, etc..

    Note: You CAN bypass that, but if you EVER add a row or column, it'll hose your links. Without naming cells, it'll link "A2", but if you add a row between 1 and 2, A2 is now a different A2... BUT, if you named it MyName, MyName is ALWAYS and forever known as MyName.

    Once you've named the field [let's say "A10" is named "MyName"], so you'd select A10/MyName and hit Copy.

    Go into Word, then hit Edit->Paste Special->Unformatted Text
    ----If you want it "linked" [Word changes when Excel changes...], make sure to hit that button, too.

    You now have whatever's in Excel in your Word document. Repeat for all 20 of your fields.

    The "tricky" part comes in when you want to rename or otherwise re-use that Excel file. The links you just set up are all "static" [fixed] path. For example, if your Excel file was C:\Documents and Settings\My Excel Documents\BlahBlahBlah\ExcelTemplate.xls your links will be set to go there. You can, however, click on the linked text (in Word) and right-click -> Show field codes. That'll expand your linked text into the code that runs the whole show - fairly daunting the first time you see it... Basically, it is code that says "what's happening", "from what source", "what data" and potentially various "switches" for formatting purposes. If your path changes [e.g., if you save the Excel out to C:\Excel\MyNewExcel.xls], all you have to do is find/replace everything between the parentheses (the old path...) and replace it with the new path.

    If you're working across a network, your path will look odd, like \\\\server\\volume\\directory\\subdirectory\\sub-subdirectory\\MyOldExcel.xls

    Just keep that same \\\\ \\ formatting when you change your path. No biggie.

    I'm currently working on a macro that I'll use from a "template" Excel file that will:

    1) Open a new doc, or a .dot template
    2) Prompt for a location to save both templates (both the word document and the excel document)
    3) Find/Replace the old path (to a known template location where my Excel template resides) with whatever my "Save As..." location and name was.

    Once I'm done with that, I'll post the code on here for you. After that, we should both be able to just open up an Excel "template", hit a button, browse to a location, then sit back and let it do its thing.

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,722

    Re: How to Develop Macro on Excel which opens a word document and feed data into table

    @ hoop & mithun
    you can open excel from word or word from excel, either using early binding (which means a reference to the office application you want to open) or by late binding using createobject("excel.application") or createobject("myfile.xls")

    check out si the geek tutorial (FAQ) on automating office applications
    Last edited by westconn1; Aug 1st, 2008 at 04:17 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6
    Member
    Join Date
    May 2008
    Posts
    36

    Re: How to Develop Macro on Excel which opens a word document and feed data into table

    Thanks Westconn1. If possible - please look at my code (the red parts) and tell me what I need to do to get Word and Excel to open up both "new" documents, then close the original/template Excel file from which the macro is fired. If it isn't possible, its no big deal, but that'd light the candle on my cake.




    As promised, I'm adding the following code. The scenario is this:

    1) You open the TEMPLATE Excel file [and fill in at least Cell D24 (named as such in MY Excel template... see #5 below)];
    2) You run this macro;
    3) The macro asks you where to save your new word and excel files (based off of the template you're in and a document template) and what BOTH file's name should be [same name...];
    4) The macro saves the new Excel;
    5) The macro opens a document template [in my Excel template, "D24" is a pull-down box named "bank" or "golf_course", and I have document template (.dot) files NAMED EXACTLY THE SAME as the options in my pull-down box in my template repository ["J:\REPORT RESOURCES\TEMPLATES\"];
    6) The macro does a find/replace for the Word document's existing path [TO THE TEMPLATE EXCEL file] and replaces it with the full path of where you told the new Word and Excel files to go;
    7) The macro closes the new Word document.

    In whatever directory you told it to store the new word and excel files, you'll see both files, ready to be used. Again, open Excel first, then Word. ACTUALLY ... Only open Excel at first. Make ALL of your changes (from "template" to "specific" for the new Excel file), then open Word to "sync" all of your links up. Past this point, no more major insertions or deletions should be done in Excel.

    In Word, Hit Ctrl-A to select the whole word document, and right-click "Update Fields". Wait 2-3 minutes (depending on how many links you have - I usually have 400 - 500 and it takes 3:00+- on an AMD 3000 and 0:50+- on a P4 2.4 Dual Core machine).


    Anywho, here's the "AssembleReport" code that I just finished.
    To use this, just go to Tools->Macros->Visual Basic Editor, find your file on the top left pane, right click to insert a new module, then copypasta all of this into the white pane on the right. Save, close VB Editor, then you're set to (whenever everything's set up... doc templates, changing my "D24" to your whatever, changing my "J:\REPORT RESOURCES\TEMPLATES" to your whatever, etc.) to run the thing by simply going to Tools->Macros-> Highlight "AssemlbeReport" and then hit "Run". Sit back, and 2 minutes later you'll have a new word and excel with all links being correct, etc.

    That's the 10-minute run-down on the last few months of my life, learning and cobbling-together VB and trying to automate Word and Excel for cranking out 100-page appraisal reports. I hope it is of help (and helps shortcut the process) for anybody else out there in la-la-land that's reading.

    Code:
    Sub AssembleReport()
    '
    Dim sTemplates As String
    Dim sSaveName As String
    Dim sGetName As String
    Dim sLookFor As String
    Dim oField As Object
    Dim oWord As Object
    Set oWord = CreateObject("Word.Application")
    
    'Get the location of the Template directory
    With CreateObject("WScript.Shell")
    sTemplates = .ExpandEnvironmentStrings("J:\REPORT RESOURCES\TEMPLATES\")
    End With
    
    'Get this workbook's network path (What Word looks for)
    sLookFor = SDblSlash(SToNetworkName(ThisWorkbook.FullName))
    
    'Get the name/location for saving
    sSaveName = SToNetworkName(SGetLoc)
    ActiveWorkbook.SaveCopyAs sSaveName & ".xls"
    
    'Extract (from Excel) the document template name to open
    sGetName = Worksheets("INPUTS").Range("SUBJ_PropType").Value & ".dot"
    
    
    
    'Open the Word template
    With oWord.Documents.Open(sTemplates & sGetName)
    oWord.Visible = True
    
    'Update the field code paths in the new Word document (working document)
    Application.ScreenUpdating = False
    
    For Each oField In .Fields
    Application.StatusBar = "Updating field " & oField.Index
    oField.Code.Text = Replace(oField.Code, sLookFor, """" & SDblSlash(sSaveName) & ".xls""", , , 1)
    oField.Code.Text = Replace(oField.Code, SDblSlash(ThisWorkbook.FullName), "'" & SDblSlash(sSaveName) & ".xls""", , , 1)
    oField.Code.Text = Replace(oField.Code, """""", """")
    
    Next
    Application.ScreenUpdating = True
    Application.StatusBar = ""
    
    'Saving the link-corrected Word document
    .SaveAs sSaveName & ".doc"
    
    End With
    
    
    'Open the new Word document (working document)
    '???
    
    'Open the new Excel document (working document)
    '???
    
    'Close the Excel template (which ran the macro)
    '??? - Is this possible, since the macro still needs to run?
    
    
    'Cleanup
    If Not oWord Is Nothing Then
    oWord.Quit 0
    End If
    Set oWord = Nothing
    End Sub
    
    Private Function SGetLoc()
    'Save dialog
    SGetLoc = Application.GetSaveAsFilename()
    
    'Remove any extension (I.E. .xls)
    If InStrRev(SGetLoc, ".") Then _
    SGetLoc = Left(SGetLoc, InStrRev(SGetLoc, ".") - 1)
    End Function
    
    Private Function SDblSlash(sIn As String)
    SDblSlash = Replace(sIn, "\", "\\")
    End Function
    
    Private Function SToNetworkName(sIn As String)
    Dim sName As String
    Dim i As Integer
    Dim cDrives As Object
    
    sName = sIn
    If Mid(sName, 2, 1) <> ":" Then
    'Not a drive letter, mapped or otherwise
    SToNetworkName = sName
    Exit Function
    End If
    
    Set cDrives = CreateObject("WScript.Network").EnumNetworkDrives
    With cDrives
    For i = 0 To .Count - 1 Step 2
    If InStr(1, Mid(sName, 1, 2), .Item(i), 1) And CBool(Len(.Item(i))) Then
    'Found the network drive we're using
    sName = .Item(i + 1) & Mid(sName, 3)
    Exit For
    End If
    Next
    End With
    SToNetworkName = sName
    End Function

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,722

    Re: How to Develop Macro on Excel which opens a word document and feed data into table

    to open new word document to work with
    set newdoc = oword.documents.add ' then work with the noewdoc object

    to open excel
    vb Code:
    1. set myxl = createobject("excel.application")
    2. set mybook = myxl.workbooks.add
    3. set mysht = mybook.sheets("Sheet1")
    4. mysht.name = "this is my sheet 1"  'change name of sheet or somthing' most of your work will be done with the sheet object
    as all this is done using late binding, all the above variables should be declared as object

    to save and close
    vb Code:
    1. mybook.save
    2. mybook.close
    3. myxl.quit
    4. objword.quit
    if you don't want to be prompted for file name and path use SaveAs and supply those arguments
    mybook.saveas somenamewithfullpath

    all places in your code you use application shoud specify which application
    objwork.screenupdating = true 'application object of word
    myxl.statusbar = "working" 'application object of excel

    to avoid confusion

    set all objects to nothing when finished with, including in your functions
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8
    Member
    Join Date
    May 2008
    Posts
    36

    Re: How to Develop Macro on Excel which opens a word document and feed data into table

    Thanks for the reply.

    I think I miscommunicated the intent by the use of "new". I meant "How do I open the document and excel file that were created by my macro?"

    Its not a big thing - I can wait for the macro to run, shut down my template (Excel) and then open up the newly-created word and excel docs, but I thought that since I had the full path and the save name already loaded up that it'd only make sense to automate that process as well.

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,722

    Re: How to Develop Macro on Excel which opens a word document and feed data into table

    i am not quite sure what you are asking here, but if you want the workbook or document open and displayed when your macro finishes, then don't close the book, but set the application to visible
    vb Code:
    1. mybook.save
    2. myxl.visible = true
    3. set mybook = nothing
    4. set myxl = nothing    ' myxl will remain open and displayed to user, but you have now lost control
    to get control again if needed
    getobject ("myworkbook.xls")
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10
    Member
    Join Date
    May 2008
    Posts
    36

    Re: How to Develop Macro on Excel which opens a word document and feed data into table

    Quote Originally Posted by westconn1
    i am not quite sure what you are asking here, but if you want the workbook or document open and displayed when your macro finishes, then don't close the book, but set the application to visible
    vb Code:
    1. mybook.save
    2. myxl.visible = true
    3. set mybook = nothing
    4. set myxl = nothing    ' myxl will remain open and displayed to user, but you have now lost control
    to get control again if needed
    getobject ("myworkbook.xls")
    My apologies.

    Let me summarize the macro's steps, then it'll be clearer.

    1) The macro fires from TEMPLATE.xls
    2) The macro saves a copy of TEMPLATE.xls as sSaveName
    3) The macro opens a Word Document Template and saves as the same sSaveName.
    4) Both sSaveName (.doc and .xls) are "newly-created" by the macro.
    5) At the end of the macro, the only thing open/visible is the TEMPLATE.xls worksheet. The newly-created sSaveName'd files are closed.

    Since I already have the full path and sSaveName as variables, I'd like to have the macro:

    1) Open sSaveName.xls
    2) Open sSaveName.doc
    3) Save and close TEMPLATE.xls [from which the macro fired - the template, now not being necessary (as it is a template...) can be closed down - leaving me with my "newly-created" xls and doc [both of which are SaveAs's of template files]

  11. #11
    Head Hunted anhn's Avatar
    Join Date
    Aug 2007
    Location
    Australia
    Posts
    3,669

    Re: How to Develop Macro on Excel which opens a word document and feed data into table

    Quote Originally Posted by Hoop
    Let me summarize the macro's steps, then it'll be clearer.

    1) The macro fires from TEMPLATE.xls
    2) The macro saves a copy of TEMPLATE.xls as sSaveName
    3) The macro opens a Word Document Template and saves as the same sSaveName.
    4) Both sSaveName (.doc and .xls) are "newly-created" by the macro.
    5) At the end of the macro, the only thing open/visible is the TEMPLATE.xls worksheet. The newly-created sSaveName'd files are closed.

    Since I already have the full path and sSaveName as variables, I'd like to have the macro:

    1) Open sSaveName.xls
    2) Open sSaveName.doc
    3) Save and close TEMPLATE.xls [from which the macro fired - the template, now not being necessary (as it is a template...) can be closed down - leaving me with my "newly-created" xls and doc [both of which are SaveAs's of template files]
    1 & 3) Instead of: ActiveWorkbook.SaveCopyAs sSaveName & ".xls"
    use:
    ActiveWorkbook.Save
    ActiveWorkbook.SaveAs sSaveName & ".xls"

    your "TEMPLATE.xls" workbook is now saved and closed with a new copy "sSaveName.xls" is openned.
    The macro keeps running in new workbook.

    2) If you don't use this part of code then "sSaveName.doc" is still openned.
    Code:
    'Cleanup
    If Not oWord Is Nothing Then
    oWord.Quit 0
    End If
    Set oWord = Nothing
    • Don't forget to use [CODE]your code here[/CODE] when posting code
    • If your question was answered please use Thread Tools to mark your thread [RESOLVED]
    • Don't forget to RATE helpful posts

    • Baby Steps a guided tour
    • IsDigits() and IsNumber() functions • Wichmann-Hill Random() function • >> and << functions for VB • CopyFileByChunk

  12. #12
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,555

    Re: How to Develop Macro on Excel which opens a word document and feed data into table

    How to Read/Write/Modify a Word Table:
    http://www.vbforums.com/showthread.php?t=402098

    How to Automate an Office Application:
    http://www.vbforums.com/showthread.php?t=406640
    (note even though its for VB6, its the same for automating an Office app from within another Office app).
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Star Wars Gangsta Rap Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Corsair H100i v2 water cooler, Geforce GTX1060, Samsung M.2 500 GB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2010, VS 2010

  13. #13
    Member
    Join Date
    May 2008
    Posts
    36

    Re: How to Develop Macro on Excel which opens a word document and feed data into table

    Thanks guys!

    Sorry for semi-hijacking OP's thread. I can't edit the tag to set it to resolve, but I think everything has been covered on the topic, so an Admin can probably close it if necessary.

    Thanks again!

    Hoop

  14. #14
    Head Hunted anhn's Avatar
    Join Date
    Aug 2007
    Location
    Australia
    Posts
    3,669

    Re: How to Develop Macro on Excel which opens a word document and feed data into table

    Quote Originally Posted by Hoop
    Sorry for semi-hijacking OP's thread. I can't edit the tag to set it to resolve, but I think everything has been covered on the topic, so an Admin can probably close it if necessary.
    Your problem has been solved but perhaps OP's problem has not been solved.
    • Don't forget to use [CODE]your code here[/CODE] when posting code
    • If your question was answered please use Thread Tools to mark your thread [RESOLVED]
    • Don't forget to RATE helpful posts

    • Baby Steps a guided tour
    • IsDigits() and IsNumber() functions • Wichmann-Hill Random() function • >> and << functions for VB • CopyFileByChunk

  15. #15
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,555

    Re: How to Develop Macro on Excel which opens a word document and feed data into table

    Ah so it hasnt. This is what happens

    Well until the OP comes back not much else to do.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Star Wars Gangsta Rap Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Corsair H100i v2 water cooler, Geforce GTX1060, Samsung M.2 500 GB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2010, VS 2010

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width