Consulting

Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 21 to 40 of 56

Thread: Import Worksheets Into TextBox

  1. #21
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    UserForm: Not part of, just used by, MS Applications like Word and Excel. Can use External additional Controls like Spreadsheet Control.

    Details.dotm: Contains a UserForm to hold and display Information, which UserForm does not contain a Control to effectively hold or display an Excel Table or Range.

    Details.dotm: Also contains a Word Doc with ContentControls, whose Data is entered programmatically from the UserForm via Sub "FillForm". (Rant, This sub should be named FillContentControls.) Such Sub does not yet have a method to transfer Excel Table to the Word Document.

    Problems: The UserForm doesn't contain any Controls suitable for Excel Tables or Ranges. The "reconmmended" method of inserting an Excel Table into a Word Document is to paste the table in.
    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

  2. #22
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Thanks for the breakdown of the terminology, Sam. You have made this easy to understand.

    I think if I understand your last point, that my problem might not be easily solved?

  3. #23
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I discommend downloading custom UserForm Controls.

    A UserForm Combobox can be displayed very like a gridless Excel Table, and has a Property that accesses an Excel Range by address.

    The code in Details.dotm, ThisDocument Module does not yet properly address the Excel Table Data.

    Don't close the UserForm or UserForm Object until the code in Sub FillForm (I mean sub FillControls) is finished. Use the UserForm to return the fullpath, worksheet, and Range address of the table, then copy paste it into the Document at a particular bookmark. This would not affect any current ContentControls or other UserForm Controls.
    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

  4. #24
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Frustrated that different Administrators keep moving this thread.

    I've managed to keep plugging away at this and have now got to a stage that when the user presses 'CommandButton1', the Excel Workbook named 'Triage' is opened (if it isn't already) and the contents of Worksheets named 'Markers' and 'History' copy their respective contents, then paste directly onto a word document at whatever point the cursor is at.

    Private Sub AggdBut_Click()
        
        Dim Excel As Excel.Application, Workbook As Excel.Workbook, Worksheet As Excel.Worksheet, i As Integer
        Dim ExcelOpen As Boolean
        
        On Error Resume Next
        Set Excel = GetObject(, "Excel.Application") 'Select Excel if open
        If Err.Number <> 0 Then 'If Excel is not already open, then open it
        Set Excel = CreateObject("Excel.Application")
    Else
        ExcelOpen = True   'An indicator so we know whether to close Excel or not when finished
    End If
    On Error GoTo 0
    
    'Open workbook Triage
    On Error Resume Next
    Set Workbook = Excel.Workbooks.Open(ThisDocument.Path & "/Triage.xlsm")
    Workbook.Application.DisplayAlerts = False
    If Workbook Is Nothing Then
        MsgBox "Unable to open file!"
        On Error GoTo 0
        GoTo CleanUp
    End If
    On Error GoTo 0
    
    ' First deal with the worksheet Markers
    With Workbook.Worksheets("Markers")
        'See if there's anything to copy
        If .Cells(1, 1) = vbNullString Then
            MsgBox "There is nothing to copy!"
            GoTo CleanUp
        End If
        
        .UsedRange.Copy ' Copy contents of Sheet Markers
        Selection.TypeText Text:="These are the records for the past eighteen months" & vbCr & vbCr
        
        Selection.Paste 'Paste sheet Markers into document at cursor
    End With
    Workbook.Application.DisplayAlerts = False
    WordBasic.EditOfficeClipboard
    CommandBars("Office Clipboard").Visible = False
    
    
    ' Secondly deal with the worksheet History
    With Workbook.Worksheets("History")
        'See if there's anything to copy
        If .Cells(1, 1) = vbNullString Then
            MsgBox "There is nothing to copy!"
            GoTo CleanUp
        End If
        
        .UsedRange.Copy ' Copy contents of Sheet History
        Selection.TypeText Text:="These are the records for the past eighteen months" & vbCr & vbCr
        
        Selection.Paste 'Paste sheet History into document at cursor
    End With
    Workbook.Application.DisplayAlerts = False
    WordBasic.EditOfficeClipboard
    CommandBars("Office Clipboard").Visible = False
    
    CleanUp:
    If ExcelOpen = False Then Excel.Quit 'close Excel if we started it, otherwise leave open
    Workbook.Application.DisplayAlerts = False
    
    Dim oDataObject As DataObject
    Set oDataObject = New DataObject
    oDataObject.SetText ""
    oDataObject.PutInClipboard
    
    Set oDataObject = Nothing
    End Sub
    Just needing some help now please into how to modify the above so that the contents are pasted directly into TextBox3 of my UserForm in Word.

  5. #25
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    The following allows me to paste the contents of one part of the clipboard into TextBox3 (the last of the two worksheets, "History") if I place it after
    Selection.Paste
    It's clear that the first "Markers" worksheet data gets overwritten by the "History" worksheet data.

    Dim clipboard       As MSForms.DataObject
    Set clipboard = New MSForms.DataObject
    clipboard.GetFromClipboard
    TextBox3.Text = clipboard.GetText(1)

    Could someone please give me some guidance on the process that I will need to place the two worksheets ("Markers" and "History"), along with their respective lines of pre-text ("The following markers are listed" and "These are the records for the past eighteen months") respectively into TextBox3? This should deal with CommandButton1. Although I will need to optionally repeat the process with CommandButton2 with new data. This will also need to be added to TextBox3 without removing the data added by CommandButton1.

    I'm guessing that I might need to get each of the contents of the worksheets assigned into a variable, then "join" them together, before getting this into the TextBox3?

    My VBA knowledge is not great, although I am determined to persevere!

  6. #26
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    You can't put a Table in a Textbox. You can only put a single String in a Textbox.
    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. #27
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Thanks for your reply, Sam.

    I've now modified my code as follows, but am getting a 'Compile error: Sub or Function not defined at
    S3 = GetText("formatId3")  ' No markers

    Private Sub AggdBut_Click()
        
        Dim Excel As Excel.Application, Workbook As Excel.Workbook, Worksheet As Excel.Worksheet, i As Integer
        Dim ExcelOpen As Boolean
        Dim DataObj As New MSForms.DataObject
        
        ' Set default text strings
        Dim S1 As String
        Dim S2 As String
        Dim S3 As String
        Dim S4 As String
        S1 = "The following markers are listed" & vbCr & vbCr
        S2 = "These are the records For the past eighteen months" & vbCr & vbCr
        S3 = "There are no markers" & vbCr & vbCr
        S4 = "There are no records" & vbCr & vbCr
        
        With DataObj
            .SetText S1, "FormatId1"
            .PutInClipboard
            .SetText S2, "FormatId2"
            .PutInClipboard
            .SetText S3, "FormatId3"
            .PutInClipboard
            .SetText S4, "FormatId4"
            .PutInClipboard
            S1 = vbNullString
            S2 = vbNullString
            S3 = vbNullString
            S4 = vbNullString
        End With
        
        ' Check if Excel is open
        
        On Error Resume Next
        Set Excel = GetObject(, "Excel.Application")     'Select Excel if open
        If Err.Number <> 0 Then    'If Excel is not already open, then open it
        Set Excel = CreateObject("Excel.Application")
    Else
        ExcelOpen = True        'An indicator so we know whether to close Excel or not when finished
    End If
    On Error GoTo 0
    
    ' Open workbook Triage
    
    On Error Resume Next
    Set Workbook = Excel.Workbooks.Open(ThisDocument.Path & "/Triage.xlsm")
    Workbook.Application.DisplayAlerts = False
    If Workbook Is Nothing Then
        Beep
        MsgBox "Unable To open file!"
        On Error GoTo 0
        GoTo CleanUp
    End If
    On Error GoTo 0
    
    
    ' First deal with the worksheet Markers
    
    With Workbook.Worksheets("Markers")
        'See if there's anything to copy
        If .Cells(1, 1) = vbNullString Then
            .GetFromClipboard
            S3 = GetText("formatId3")  ' No markers
            
        Else: .GetFromClipboard
            S1 = GetText("formatId1")  ' There are markers
        End If
        
    End With
    
    ' Secondly deal with the worksheet History
    With Workbook.Worksheets("History")
        'See if there's anything to copy
        If .Cells(1, 1) = vbNullString Then
            .GetFromClipboard
            S4 = GetText("formatId3")  ' No history
            
        Else: .GetFromClipboard
            S2 = GetText("formatId1")  ' There is history
        End If
        
    End With
    
    ' Paste everything that has been copied into TextBox3
    Dim S As String
        DataObj.GetFromClipboard
        S = DataObj.GetText
        TextBox3.Text = clipboard.GetText(1)
    
    CleanUp:
    If ExcelOpen = False Then Excel.Quit        'close Excel if we started it, otherwise leave open
    Workbook.Application.DisplayAlerts = False
    End Sub
    
    ' Clear clipboard
    
    Sub ClearClipboard()
        OpenClipboard (0&)
        EmptyClipboard
        CloseClipboard
    End Sub

  8. #28
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Still plugging away... Now getting a 'Runtime error 9 : Subscript out of range' on this line

    With Workbook.Worksheets("Markers")
    The worksheet exists and is spelt correctly. Argh!!

    I appreciate that I will need to address 'Dim S' issue to stop it overwriting itself, but just need to get something pasted into TextBox3 so that I know I'm on the right track.

    Private Sub AggdBut_Click()
        
        Dim Excel As Excel.Application, Workbook As Excel.Workbook, Worksheet As Excel.Worksheet, i As Integer
        Dim ExcelOpen As Boolean
        Dim DataObj As New MSForms.DataObject
        Dim MyData As DataObject
        Dim S As String
        
        ' Set default text strings
        Dim S1 As String
        Dim S2 As String
        Dim S3 As String
        Dim S4 As String
        S1 = "The following markers are listed" & vbCr & vbCr
        S2 = "These are the records for the past eighteen months" & vbCr & vbCr
        S3 = "There are no markers" & vbCr & vbCr
        S4 = "There are no records" & vbCr & vbCr
        
        With DataObj
            .SetText S1, "FormatID1"
            .PutInClipboard
            .SetText S2, "FormatID2"
            .PutInClipboard
            .SetText S3, "FormatID3"
            .PutInClipboard
            .SetText S4, "FormatID4"
            .PutInClipboard
            S1 = vbNullString
            S2 = vbNullString
            S3 = vbNullString
            S4 = vbNullString
        End With
        
        ' Check if Excel is open
        
        On Error Resume Next
        Set Excel = GetObject(, "Excel.Application")        'Select Excel if open
        If Err.Number <> 0 Then        'If Excel is not already open, then open it
        Set Excel = CreateObject("Excel.Application")
    Else
        ExcelOpen = True        'An indicator so we know whether to close Excel or not when finished
    End If
    On Error GoTo 0
    
    ' Open workbook Triage
    
    On Error Resume Next
    Set Workbook = Excel.Workbooks.Open(ThisDocument.Path & "/Triage.xlsm")
    Workbook.Application.DisplayAlerts = False
    If Workbook Is Nothing Then
        Beep
        MsgBox "Unable to open file!"
        On Error GoTo 0
        'GoTo CleanUp
    End If
    On Error GoTo 0
    
    ' First deal with the worksheet Markers
    
    With Workbook.Worksheets("Markers")
    
        'See if there's anything to copy
        If .Cells(1, 1) = vbNullString Then
            S = ("FormatID3")        ' No markers
        Else: S = ("FormatID1")      ' There are markers
        End If
    End With
    
    ' Secondly deal with the worksheet History
    With Workbook.Worksheets("History")
    
        'See if there's anything to copy
        If .Cells(1, 1) = vbNullString Then
            S = ("FormatID4")        ' No history
        Else: S = ("FormatID2")      ' There is history
        End If
    End With
    
    MyData.GetFromClipboard
    TextBox3.Text = MyData.GetText(1)
    
    End Sub

  9. #29
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    I see that you're still trying for the impossible, and I'm not going to discourage U, but U really should read and re-read SamT's advice. Anyways, it's very bad to use terms such as Excel, Workbook and/or Worksheet as variable names... you're just asking for code failure and/or unexpected results. This should help with your current concerns Good luck. Dave
    Set Workbook = Excel.Workbooks.Open(ThisDocument.Path & "/Triage.xlsm")
    Set Worksheet = Workbook.Worksheets("Markers")
    With Worksheet

  10. #30
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    need to get something pasted into TextBox3 so that I know I'm on the right track.
    Dim X As String
    X = "TextBoxes can only contain Strings," & VbCr _
       & "Not Tables"
    TextBox3 = X
    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

  11. #31
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Dave / Sam, I have been so blinkered in trying to find a solution that I think that I haven't properly acknowledged your advice.

    I understand that a table cannot be pasted into a TextBox.

    I am trying to get the contents of the worksheet into the TextBox (i.e. not bothered about the actual table elements, just the actual text held in the occupied cells). I was close with the code as per post #24, but this wasn't fully achieving my aim of allowing a string of text to be added before the input (this provides an explanation of the text being imported) and allowing this to happen with two imports via the clipboard when pressing a single CommandButton. Post #24 has got the nearest to my desired result, but for only one imported set of table data.


    So my aim is to get the following into TextBox3. Other than the two lines of fixed text, the table content will be completely different each time.


    Line of text from either 'There are no markers' (No table data to be input if true) or 'These are the markers that are shown' (with something like the following)

    High Allocated from Triage department 16/11/2020
    Low Allocated via resolution 05/11/2020
    High Allocated from Triage department 01/11/2020
    Medium Retrieved from default status 26/10/2020
    Low Allocated via resolution 24/10/2020
    Low Allocated after prior agreement 17/09/2020
    Medium Identified as likely to require further input 03/09/2020

    Second line of text from either 'There are no records' (No table data to be input if true) or 'These are the records that have been currently located' (with something like the following)

    Subject O1 Intervention was required to obtain the desired result 16/11/2020
    Alternative L3 It is highly likely that this might prove cost effective, but might need further input 05/11/2020
    Failed T2 This proved negative for all tests 01/11/2020
    Failed T3 Exceptionally high costs involved and didn't achieve the desired result 26/10/2020
    Subject B5 The nearest test to date that will only require minor adjustment 24/10/2020
    Failed S7 A complete non-starter 17/09/2020

    It doesn't matter that the text will need to be manually aligned (per column) after the TextBox3 content has been committed to the actual document (obviously if this could be achieved without intervention, then all the better).

  12. #32
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Quit trying to drive a nail with a screwdriver.

    Place a bookmark in the Word Doc and paste the Excel range at the 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

  13. #33
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    As a former engineer I get the analogy!

    Are we simply saying that the way that I was thinking is making things too complicated? My current form adds various other bits and pieces already so was looking to add this functionality to it. At the moment the user has to copy and paste the contents of each worksheet separately into TextBox3.

    Are you suggesting something along these lines to convert the tables to a range, then pasting them into TextBox3?
    https://www.mrexcel.com/board/thread...-range.367870/

    My VBA knowledge is very limited so I may still miss what you are suggesting.

  14. #34
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    As a former engineer I get the analogy!
    Who trained you, Rube Goldberg?

    Forget textbox3. Remove it from your mind. Delete it from your code. Tear the T, the X, and the 3 keys off your keyboard

    RTFM all the responses to your queries above. Search for "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

  15. #35
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Ha Ha!!

    Okay, I think I've finally got it, although I'll be opting for content control instead of bookmarks to place the data. Quite why I felt the need to "view" the contents of the two tables in my form?!?

    I'll have a bit of a go with producing some code to achieve output as per post #31

    I'll make sure I change my variable names for the Excel related items too!

  16. #36
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    What you want can actually be done. Just Parse the table directly from Excel and convert it to a Single String that a TextBox can use. I don't know, but you might have to use two Strings, one for a Forms TextBox and one for a Word ContentControl,

    You will need two loops (at Least)
    For Each Row in Table and
    For each Column (Cell) in Row

    Add a LineFeed/CarriageReturn for each Row and
    Format each Cells Value to a String with spaces to align all "columns" in the Container(s)



    What is the shortest distance between two points?





    Nobody is helping you because the shortest distance between Excel and Word is well known and documented, but you are insisting that the "Taxi" go from Los Angeles to Sacramento via Paris, France,
    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

  17. #37
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Oh dear, I think I'm confusing things again.

    I'm fully onboard with taking the direct route!- Honest!!!

    'CommandButton1' (as I'm calling it at the moment) should check for and look in the workbook called "Triage" (same folder as the Word Document that the macro is being run from), checking for and copying any cells with data in them from two worksheets ('Markers' and 'History'), adding the lines of text 'There are no markers' (if worksheet 'Markers' is empty) or 'These are the markers that are shown' (with something like the following being input from worksheet 'Markers' as an example)

    (PLEASE IGNORE THE ACTUAL TABLE ELEMENT, THIS IS NOT REQUIRED)

    High Allocated from Triage department 16/11/2020
    Low Allocated via resolution 05/11/2020
    High Allocated from Triage department 01/11/2020
    Medium Retrieved from default status 26/10/2020
    Low Allocated via resolution 24/10/2020
    Low Allocated after prior agreement 17/09/2020
    Medium Identified as likely to require further input 03/09/2020


    Then a second line of text from either 'There are no records' (if worksheet 'History' is empty) or 'These are the records that have been currently located' (with something like the following being input from worksheet 'History' as an example)

    Subject O1 Intervention was required to obtain the desired result 16/11/2020
    Alternative L3 It is highly likely that this might prove cost effective, but might need further input 05/11/2020
    Failed T2 This proved negative for all tests 01/11/2020
    Failed T3 Exceptionally high costs involved and didn't achieve the desired result 26/10/2020
    Subject B5 The nearest test to date that will only require minor adjustment 24/10/2020
    Failed S7 A complete non-starter 17/09/2020



    All the above should then be placed in the word document at the content control called 'Imported Text'. The number of columns is likely to remain constant, however the amount of data could range from a single line to approximately 150 lines or so.

    A second button called 'CommandButton2' (for now), if required by the user, will need to perform exactly the same as the above but will obviously have different content data to work with.

    Phew!

  18. #38
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Following on from Dave's post at #16, I've arrived at the following

    Option Explicit
    
    Private Sub cmdInputBut1_Click()
        Call XLTableToWord("Markers")
    End Sub
    
    Private Sub cmdInputBut2_Click()
        Call XLTableToWord("Person")
    End Sub
    Private Sub cmdInputBut3_Click()
        Call XLTableToWord("Markers1")
    End Sub
    
    Private Sub cmdInputBut4_Click()
        Call XLTableToWord("Person1")
    End Sub
    
    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
        
        Select Case True
            Case SheetName = "Markers": Selection.TypeText Text:="These are the markers shown" & vbCr & vbCr
                Selection.Paste                       'Paste sheet Markers into document at cursor
            Case SheetName = "Person": Selection.TypeText Text:="These are the records shown for the past eighteen months" & vbCr & vbCr
                Selection.Paste                       'Paste sheet Person into document at cursor
            Case SheetName = "Markers1": Selection.TypeText Text:="These are the markers shown" & vbCr & vbCr
                Selection.Paste                       'Paste sheet Markers1 into document at cursor
            Case Else
                SheetName = "Person1": Selection.TypeText Text:="These are the records shown for the past eighteen months" & vbCr & vbCr
                Selection.Paste                       'Paste sheet Person1 into document at cursor
                      
        End Select
    lbl_Exit:
        
        'Emptying the Clipboard
        Dim oData   As New DataObject
        
        oData.SetText Text:=Empty
        oData.PutInClipboard
        
        Application.ScreenUpdating = True
    End Function
    I'm getting a 'Runtime error '9': subscript out of range' error on this line

    Set objWorksheet = objExcel.Workbooks("Triage.xlsm").Sheets(SheetName)
    Hovering over (SheetName) on this line, this shows that the correct worksheet is being referenced.

    A major scratching the head moment. Can anyone suggest why this is happening and how to get around it?

    Once I get this working I can concentrate on getting the data to fill in at their respective content control points.

    Thanks!

  19. #39
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    Dim objExcel As Object
    Dim objWorksheet As Object
    HTH. Dave

  20. #40
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Sorry, adding these two lines initially produced a duplication error with these lines

    Dim objExcel    As Excel.Application
        Dim objWorksheet As Excel.Worksheet
    Comment out these two lines then produces the 'Runtime error '9': subscript out of range' on the same line again

    Set objWorksheet = objExcel.Workbooks("Triage.xlsm").Sheets(SheetName)

Posting Permissions

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