Consulting

Page 1 of 3 1 2 3 LastLast
Results 1 to 20 of 52

Thread: Import Worksheets Into TextBox

  1. #1

    Import Worksheets Into TextBox

    Wasn’t sure whether to put this under Word or Excel, but went with Word as this is where the data needs to end up.

    I have two buttons on my Word userform which I need to import a specifically named worksheet table into a specific TextBox. It will not always be necessary to use both buttons, but if they are both used, then the contents of the TextBox should not be overwritten by the other button’s action.

    The Excel Workbook will already be open and the worksheets are already named. The tables do not have a fixed number / range of cells. I can move the Dims to the start of the document if this is needed (save on repetition?) after OptionExplicit.

    Here is what I have so far, which rather helpfully does nothing when either button is pressed.

    ' Add Markers Detail
    
    Private Sub MarkersBut_Click()
        
        'Using Early Binding
        
        Dim wordApp     As Word.Application
        Dim mydoc       As Word.Document
        Dim wb          As Workbook
        Application.ScreenUpdating = False
        Set wb = ActiveSheet("Markers")
        
        ' Copying the content from active Excel worksheet named Markers
        
        ThisWorkbook.Worksheets("Markers").Range("A1").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select.Copy
        
        'Pasting into the document within TextBox3
        
        TextBox3(1).Range.Selection.PasteExcelTable _
        LinkedToExcel:=False, WordFormatting:=False, RTF:=False
        
        'Emptying the Clipboard after use
        
        CutCopyMode = False
        
    End Sub
    
    ' Add Person Detail
    
    Private Sub PersonBut_Click()
        
        'Using Early Binding
        
        Dim wordApp     As Word.Application
        Dim mydoc       As Word.Document
        Dim wb          As Workbook
        Application.ScreenUpdating = False
        Set wb = ActiveSheet("Person")
        
        ' Copying the content from active Excel worksheet named Person
        
        ThisWorkbook.Worksheets("Person").Range("A1").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select.Copy
        
        'Pasting into the document within TextBox3
        
        TextBox3(1).Range.Selection.PasteExcelTable _
        LinkedToExcel:=False, WordFormatting:=False, RTF:=False
        
        'Emptying the Clipboard after use
        
        CutCopyMode = False
        
    End Sub

  2. #2
    Still plugging away at this one. At the moment I'm getting a 'Compile error: Variable not defined' here, when pressing the first button (haven't even bothered with the second button yet) :-

    'Create reference to object we want to copy
        
        Set objWorksheet = ActiveWorkbook.Worksheets("Markers")
    This is the code that I have for both buttons :-

    ' Add Markers Detail
    
    Private Sub AggrievedBut_Click()
        
        'Using Early Binding
        
        Dim WrdApp      As Word.Application
        Dim WrdDoc       As Word.Document
        Dim WrdTbl      As Word.Table
        
        Application.ScreenUpdating = False
        
        'Create reference to object we want to copy
        
        Set objWorksheet = ActiveWorkbook.Worksheets("Markers")
        
        ' Copying the content from Excel worksheet named Markers
        
        ThisWorkbook.Worksheets("Markers").Range("A1").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select.Copy
        
        'Pasting into the document within TextBox3
        
        TextBox3(1).Range.Selection.PasteExcelTable _
        LinkedToExcel:=False, WordFormatting:=True, RTF:=False
        
        'Emptying the Clipboard
        
        CutCopyMode = False
        
    End Sub
    
    ' Add Person Detail
    
    Private Sub PersonBut_Click()
        
        'Using Early Binding
        
        Dim WrdApp      As Word.Application
        Dim WrdDoc       As Word.Document
        Dim WrdTbl      As Word.Table
        
        Application.ScreenUpdating = False
        
        'Create reference to object we want to copy
        
        Set objWorksheet = ActiveWorkbook.Worksheets("Person")
        
        ' Copying the content from Excel worksheet named Person
        
        ThisWorkbook.Worksheets("Person").Range("A1").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select.Copy
        
        'Pasting into the document within TextBox3
        
        TextBox3(1).Range.Selection.PasteExcelTable _
        LinkedToExcel:=False, WordFormatting:=True, RTF:=False
        
        'Emptying the Clipboard
        
        CutCopyMode = False
        
    End Sub
    I can forsee that even if I could get one of the buttons to work, then clicking on the other to add another table will overwrite the previous one in my Word document's TextBox3.

  3. #3
    Here's my latest attempt. I've managed to lose the 'Variable not defined' error. I'm now getting a 'Compile Error : Wrong number of arguments or invalid property assignment' where I've highligted. My VBA knowledge is now exhausted and I'm asking for some help please!

    Private Sub MarkersBut_Click()
        
        Dim objExcel    As Excel.Application
        Dim objWorkbook As Excel.Workbook
        Dim objWorksheet As Excel.worksheet
        Dim objTable    As Table
        Dim Worksheets  As Worksheets
        
        Application.ScreenUpdating = False
        
        Set objExcel = CreateObject("Excel.Application")
        
        'Create reference to object we want to copy
        
        Set objWorksheet = ActiveWorkbook.Worksheets("Markers")
        
        ' Copying the content from Excel worksheet named Markers
        
        ThisWorkbook.Worksheets("Markers").Range("A1").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select.Copy
        
        'Pasting into the document within TextBox3
        
        TextBox3(1).Range.Selection.PasteExcelTable _
        LinkedToExcel:=False, WordFormatting:=True, RTF:=False
        
        'Emptying the Clipboard
        
        Set objWorksheet = Nothing
        Set objWorkbook = Nothing
        Set objExcel = Nothing
        
        Application.ScreenUpdating = True
        
    End Sub
    
    Private Sub PersonBut_Click()
        
        Dim objExcel    As Excel.Application
        Dim objWorkbook As Excel.Workbook
        Dim objWorksheet As Excel.worksheet
        Dim objTable    As Table
        Dim Worksheets  As Worksheets
        
        Application.ScreenUpdating = False
        
        Set objExcel = CreateObject("Excel.Application")
        
        'Create reference to object we want to copy
        
        Set objWorksheet = ActiveWorkbook.Worksheets("Person")
        
        ' Copying the content from Excel worksheet named Person
        
        ThisWorkbook.Worksheets("Person").Range("A1").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select.Copy
        
        'Pasting into the document within TextBox3
        
        TextBox3(1).Range.Selection.PasteExcelTable _
        LinkedToExcel:=False, WordFormatting:=True, RTF:=False
        
        'Emptying the Clipboard
        
        Set objWorksheet = Nothing
        Set objWorkbook = Nothing
        Set objExcel = Nothing
        
        Application.ScreenUpdating = True
        
    End Sub

  4. #4
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,303
    Location
    VBA errors report the code where the error causes the compiler to fail, not where the coding error is.

    "ThisWorkbook" is a code word for "The workbook this code is in" and your code is in a Word Doc.

    You have already set objWorksheet to a specific sheet in a specific Workbook, so substitute objWorksheet for
    ThisWorkbook.Worksheets("Xxxxxx")
    in both subs

        ' Copying the content from Excel worksheet named Xxxxxxx
        
        With objWorksheet
            .Range("A1").Select
            .Range(Selection, Selection.End(xlToRight)).Select
            .Range(Selection, Selection.End(xlDown)).Select.Copy
        End with
    My personal preference would be to try
    ' Copying the content from Excel worksheet named Xxxxxxx
    objWorksheet.Range("A1").CurrentRegion.Copy
    Last edited by SamT; 10-24-2020 at 03:40 PM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    Many thanks for looking at this one, SamT.

    I've made the changes as suggested and am now getting a 'Compile error: Invalid use of property' here

    ThisWorkbook.Worksheets ("Markers")
    Here is the revised code for both Subs

    Private Sub MarkersBut_Click()
        
        Dim objExcel    As Excel.Application
        Dim objWorkbook As Excel.Workbook
        Dim objWorksheet As Excel.Worksheet
        Dim objTable    As Table
        Dim Worksheets  As Worksheets
        
        Application.ScreenUpdating = False
        
        Set objExcel = CreateObject("Excel.Application")
        
        'Create reference to object we want to copy
        
        ThisWorkbook.Worksheets ("Markers")
        
        ' Copying the content from Excel worksheet named Markers
        
        objWorksheet.Range("A1").CurrentRegion.Copy
        
        'Pasting into the document within TextBox3
        
        TextBox3(1).Range.Selection.PasteExcelTable _
        LinkedToExcel:=False, WordFormatting:=True, RTF:=False
        
        'Emptying the Clipboard
        
        Set objWorksheet = Nothing
        Set objWorkbook = Nothing
        Set objExcel = Nothing
        
        Application.ScreenUpdating = True
        
    End Sub
    
    Private Sub PersonBut_Click()
        
        Dim objExcel    As Excel.Application
        Dim objWorkbook As Excel.Workbook
        Dim objWorksheet As Excel.Worksheet
        Dim objTable    As Table
        Dim Worksheets  As Worksheets
        
        Application.ScreenUpdating = False
        
        Set objExcel = CreateObject("Excel.Application")
        
        'Create reference to object we want to copy
        
        ThisWorkbook.Worksheets ("Person")
        
        ' Copying the content from Excel worksheet named Person
        
        objWorksheet.Range("A1").CurrentRegion.Copy
        
        'Pasting into the document within TextBox3
        
        TextBox3(1).Range.Selection.PasteExcelTable _
        LinkedToExcel:=False, WordFormatting:=True, RTF:=False
        
        'Emptying the Clipboard
        
        Set objWorksheet = Nothing
        Set objWorkbook = Nothing
        Set objExcel = Nothing
        
        Application.ScreenUpdating = True
        
    End Sub

  6. #6
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,303
    Location
    Post #4
    "ThisWorkbook" is a code word for "The [EXCEL] workbook this code is in" and your code is in a Word Doc.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  7. #7
    Okay Sam, I understand the point that you make. I might be missing something really obvious, but how do I input the worksheet "Markers" from the workbook "Triage" into my word document macro?

    Apologies if I am really missing the obvious. I've commented out the "ThisWorkbook" lines for the time being.

    Private Sub MarkersBut_Click()
        
        Dim objExcel    As Excel.Application
        Dim objWorkbook As Excel.Workbook
        Dim objWorksheet As Excel.Worksheet
        Dim objTable    As Table
        Dim Worksheets  As Worksheets
        
        Application.ScreenUpdating = False
        
        Set objExcel = CreateObject("Excel.Application")
        
        ' Create reference to object we want to copy
        
        ' ThisWorkbook.Worksheets ("Markers")
        
        ' Copying the content from Excel worksheet named Markers
        
        objWorksheet.Range("A1").CurrentRegion.Copy
        
        ' Pasting into the document within TextBox3
        
        TextBox3(1).Range.Selection.PasteExcelTable _
        LinkedToExcel:=False, WordFormatting:=True, RTF:=False
        
        'Emptying the Clipboard
        
        Set objWorksheet = Nothing
        Set objWorkbook = Nothing
        Set objExcel = Nothing
        
        Application.ScreenUpdating = True
        
    End Sub
    
    Private Sub PersonBut_Click()
        
        Dim objExcel    As Excel.Application
        Dim objWorkbook As Excel.Workbook
        Dim objWorksheet As Excel.Worksheet
        Dim objTable    As Table
        Dim Worksheets  As Worksheets
        
        Application.ScreenUpdating = False
        
        Set objExcel = CreateObject("Excel.Application")
        
        ' Create reference to object we want to copy
        
        ' ThisWorkbook.Worksheets ("Person")
        
        ' Copying the content from Excel worksheet named Person
        
        objWorksheet.Range("A1").CurrentRegion.Copy
        
        ' Pasting into the document within TextBox3
        
        TextBox3(1).Range.Selection.PasteExcelTable _
        LinkedToExcel:=False, WordFormatting:=True, RTF:=False
        
        ' Emptying the Clipboard
        
        Set objWorksheet = Nothing
        Set objWorkbook = Nothing
        Set objExcel = Nothing
        
        Application.ScreenUpdating = True
        
    End Sub

  8. #8
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,303
    Location
    Private Sub MarkersBut_Click()    Dim objExcel    As Excel.Application
        Dim objWorksheet As Excel.Worksheet
        
        Application.ScreenUpdating = False
        
        Set objExcel = CreateObject("Excel.Application")
        objExcel.Workbooks.open FullPath & "Triage.xlsx"
        Set objWorksheet = objExcel.Workbooks("Triage.xlsx").Sheets("Markers")
    
        objWorksheet.Range("A1").CurrentRegion.Copy
    
        objExcel.Workbooks("Triage.xlsx").Close
        Set objWorksheet = Nothing
        Set objExcel = Nothing
    I'm not 100% that is correct...
    At this point, the Clipboard contains the table. I don't know how to put it into Word.

    I moved the thread to the Word Forum.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  9. #9
    Thanks again, Sam.

    I've tweaked your suggested code as below, as the Workbook is in the same folder as the Word document.

    Private Sub MarkersBut_Click()
        
        Dim objExcel    As Excel.Application
        Dim objWorksheet As Excel.Worksheet
        
        Application.ScreenUpdating = False
        
        Set objExcel = CreateObject("Excel.Application")
        objExcel.Workbooks.Open (ThisDocument.Path & "/Triage.xlsm")
        Set objWorksheet = objExcel.Workbooks("Triage.xlsm").Sheets("Markers")
    
        objWorksheet.Range("A1").CurrentRegion.Copy
    
        objExcel.Workbooks("Triage.xlsm").Close
        Set objWorksheet = Nothing
        Set objExcel = Nothing
        
        'Pasting into the document within TextBox3
        
        TextBox3(1).Range.Selection.PasteExcelTable _
        LinkedToExcel:=False, WordFormatting:=True, RTF:=False
        
        'Emptying the Clipboard
        
        Set objWorksheet = Nothing
        Set objWorkbook = Nothing
        Set objExcel = Nothing
        
        Application.ScreenUpdating = True
        
    End Sub
    
    Private Sub PersonBut_Click()
        
        Dim objExcel    As Excel.Application
        Dim objWorksheet As Excel.Worksheet
        
        Application.ScreenUpdating = False
        
        Set objExcel = CreateObject("Excel.Application")
        objExcel.Workbooks.Open (ThisDocument.Path & "/Triage.xlsm")
        Set objWorksheet = objExcel.Workbooks("Triage.xlsm").Sheets("Person")
    
        objWorksheet.Range("A1").CurrentRegion.Copy
    
        objExcel.Workbooks("Triage.xlsm").Close
        Set objWorksheet = Nothing
        Set objExcel = Nothing
        
        'Pasting into the document within TextBox3
        
        TextBox3(1).Range.Selection.PasteExcelTable _
        LinkedToExcel:=False, WordFormatting:=True, RTF:=False
        
        'Emptying the Clipboard
        
        Set objWorksheet = Nothing
        Set objWorkbook = Nothing
        Set objExcel = Nothing
        
        Application.ScreenUpdating = True
        
    End Sub
    I just need some help with getting the two buttons on my Word userform which I need to import the table into the specified TextBox. It will not always be necessary to use both buttons, but if they are both used, then the contents of the TextBox should not be overwritten by the other button’s action.

    Once this works, I'll need to repeat the process with another two buttons, but one step at a time.

    Thanks!

  10. #10
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    714
    Location
    I guess I'm going to address the seemingly obvious elephant in the room, I've been wrong many times before but I don't think that it's possible to paste an XL table to a text box and if even if U could, why would U? I don't see any reference to listobjects (XL tables) in the code above and both subs above appear identical? What do U really want to do... just display an XL tables on a word userform? Why not just generate a picture of the table and paste it in an image control or frame? Maybe there's an easier method to achieve you're desired outcome rather than the one you're requesting. HTH. Dave

  11. #11
    At the moment I am copying and pasting the (two) generated Excel tables into the textbox. Yes, it seems to remove the borders of the tables, but this is what is needed.

    I then add some required text to explain the tables (still in the TextBox), complete the rest of my form, then produce the word document. Yes, I agree that this seems a rather convoluted way of producing this, but the final step is to copy all the content of the word document and paste into another bespoke package for which I have no control over.

    The main advantage of using this form is that it lays things out correctly, with all the required content and the inputter doesn't miss anything out.

    A picture object will not copy into the other package.

  12. #12
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    714
    Location
    I guess don't knock it until U try it and if it works keep doing it would apply here. Back to your needs, I don't quite follow your overwriting concerns and/or how 1 button may overwrite the other... it seems like you need to add some module level boolean variable to act as a key/switch. Maybe in combination with some kind of textbox change event? I see that the above code for the 2 buttons are actually slightly different and maybe could be changed into 1 function which may be useful....
    Public Function XLTableToWord(SheetName As String)
    Dim objExcel    As Excel.Application
    Dim objWorksheet As Excel.Worksheet
    
    Application.ScreenUpdating = False
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Workbooks.Open (ThisDocument.Path & "/Triage.xlsm")
    Set objWorksheet = objExcel.Workbooks("Triage.xlsm").Sheets(SheetName)
    objWorksheet.Range("A1").CurrentRegion.Copy
    objExcel.Workbooks("Triage.xlsm").Close
    Set objWorksheet = Nothing
    Set objExcel = Nothing
    
    
    'Pasting into the document within TextBox3
    TextBox3(1).Range.Selection.PasteExcelTable _
    LinkedToExcel:=False, WordFormatting:=True, RTF:=False
    
    
    'Emptying the Clipboard
    Set objWorksheet = Nothing
    Set objWorkbook = Nothing
    Set objExcel = Nothing
    
    
    Application.ScreenUpdating = True
    End Function
    To operate...
    Call XLTableToWord("Markers")
    Call XLTableToWord("Person")
    If you could post a representative XL file with a table that you're using, I'm guessing that SamT, myself or others could create a Word userform to trial and achieve your desired outcome. HTH. Dave

  13. #13
    I like the idea of having two separate buttons to import the necessary data into the Textbox, with each button importing the detail held in "Markers" and "Person" (the second button to import a different set of details if required).

    There would need to be a fallback of entering a line of text if there was no data in either of the two imported worksheets, along the lines of "No data available".

    I've attached the two files, but have had to use dummy text for the workbook to protect sensitive information
    Attached Files Attached Files

  14. #14
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    714
    Location
    The details.docm file doesn't seem to want to open? I'll create my own and kick it around. Dave

  15. #15
    I always seem to have trouble adding my word Template files. I changed the .dotm extension to a .docm to get it to upload. Might open if you change it back?

  16. #16
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    714
    Location
    I can't open the document to save as a template. Anyways, a few things. Your table is created by an external data source which is given the named range "Externaldata_1" which can be referred to when copying. Where is your textbox? On the userform or on the document? Either way, I cannot get anymore than 1 line of the copied range to paste in the textbox even though the whole table/named range has been copied to the clipboard. Sorry but I'm giving up. I'll post the code that works to copy the named range/table and pastes the 1st line/row to both a userform textbox and document textbox. The userform has 1 textbox (textbox1) and 2 command buttons and the document has 1 textbox (textbox1). Good luck. HTH. Dave
    'userform code
    Private Sub CommandButton1_Click()
    Call XLTableToWord("Markers")
    End Sub
    
    
    Private Sub CommandButton2_Click()
    Call XLTableToWord("Person")
    End Sub
    
    
    Public Function XLTableToWord(SheetName As String)
    Dim objExcel As Object
    Dim objWorksheet As Object
    'Application.ScreenUpdating = False
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Workbooks.Open (ThisDocument.Path & "\Triage.xlsm")
    Set objWorksheet = objExcel.Workbooks("Triage.xlsm").Sheets(SheetName)
    objWorksheet.Range("Externaldata_1").Copy
    objExcel.Workbooks("Triage.xlsm").Close
    Set objWorksheet = Nothing
    objExcel.Quit
    Set objExcel = Nothing
    
    
    UserForm1.TextBox1.Paste
    ActiveDocument.TextBox1.Paste
    
    
    'Emptying the Clipboard
    '***this DOES NOT empty the clipboard
    'Set objWorksheet = Nothing
    'Set objWorkbook = Nothing
    'Set objExcel = Nothing
    'Application.ScreenUpdating = True
    End Function

  17. #17
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,303
    Location
    It may be that you have to use PasteSpecial to get access to the Clipboard. It may be that Cut, Copy, and Paste are builtin to Word.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  18. #18
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,303
    Location
    Open a DotM for editing.
    Open word. Tell Word to open a file. Set it to Open Only All Word Templates. Open the Details.dotm file



    The issue at this (VBAX Thread) stage of program flow is pasting an Excel Range into a VBA UserForm Textbox Control.

    When the UserForm is done, the issue will be reading an improper UF Control into the Document's ContentControl, a text range.

    I suspect the solution will involve a UF Combo Box control or a UF Spreadsheet Control To display the table on the Form and actually copying the Excel Table to a Word BookMark
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  19. #19
    My thanks to Dave for taking the time to try and find a solution.

  20. #20
    Quote Originally Posted by SamT View Post
    Open a DotM for editing.
    Open word. Tell Word to open a file. Set it to Open Only All Word Templates. Open the Details.dotm file



    The issue at this (VBAX Thread) stage of program flow is pasting an Excel Range into a VBA UserForm Textbox Control.

    When the UserForm is done, the issue will be reading an improper UF Control into the Document's ContentControl, a text range.

    I suspect the solution will involve a UF Combo Box control or a UF Spreadsheet Control To display the table on the Form and actually copying the Excel Table to a Word BookMark
    Thanks for these words of wisdom, Sam. I only wish I knew what this all meant!!

    Oh and to answer Dave's earlier question - the TextBox is in a UserForm.

Posting Permissions

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