Results 1 to 10 of 10

Thread: Copy range of cells (Excel) to textbox in vb6 (not vba userform) and vice versa

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2023
    Posts
    7

    Copy range of cells (Excel) to textbox in vb6 (not vba userform) and vice versa

    I opened a workbook in the background (Application.Visible = False) via vb6. The sheet has a column of only numbers with 43,000 rows. I need to process the numbers and write the resulting numbers in the cells.

    One way to do is: (I know how to do it but it takes a lot of time)

    For i = 1 To myCells.Cells.Count
    'Process the numbers and write the resulting numbers in the cells
    Next

    I think the faster way is:

    1. Copy the entire column (range) in a textbox on the vb6 Form
    (like we do manually such as select the entire column, press Ctrl + C, go to textbox on vb6 form and press Ctrl + V.
    2. Split the text in an array.
    3. Process the numbers and write the resulting numbers in textbox
    4. Copy the entire textbox back to the same column (range)
    (like we do manually such as copy the entire text of textbox on vb6 form and paste in the sheet column.)

    I have tried the following methods to perform above steps:

    a = Sheet.Cells(Rows.Count, "A").End(xlUp).Row
    Sheet.Range("B2:B" & a).Select
    Exap.SendKeys ("^c")
    Text1.Text = Clipboard.GetText

    Clipboard.Clear
    Clipboard.SetText (Sheet.Range("B2:B" & a))
    Text1.Text = Clipboard.GetText

    Varray = Sheet.Range("B2:B" & a).Value
    Text1.Text = ""
    For i = 0 To UBound(Varray) - 1
    'Process the numbers and write the resulting numbers in the cells
    Next

    BUT any of the above mehods does not work. Please suggest how to perform these steps.

    Many thanks

  2. #2
    Addicted Member
    Join Date
    Jul 2022
    Posts
    167

    Re: Copy range of cells (Excel) to textbox in vb6 (not vba userform) and vice versa

    If you're just needing to process the numbers via code, then why not use VBA in Excel instead of using VB6 from outside of Excel? This way you're taking all the complexity of getting two systems to interact.

  3. #3

    Thread Starter
    New Member
    Join Date
    Nov 2023
    Posts
    7

    Re: Copy range of cells (Excel) to textbox in vb6 (not vba userform) and vice versa

    Quote Originally Posted by jdelano View Post
    If you're just needing to process the numbers via code, then why not use VBA in Excel instead of using VB6 from outside of Excel? This way you're taking all the complexity of getting two systems to interact.
    Thanks for the reply. Actually, my entire application is VB6 based and I cannot move to vba for only this operation.

    Please point out any way-out to perform the steps as explained.

    Thanks

  4. #4
    Addicted Member
    Join Date
    Jul 2022
    Posts
    167

    Re: Copy range of cells (Excel) to textbox in vb6 (not vba userform) and vice versa

    Ah, I see. There is much more to the Excel-berg then ;-) Which version of Excel is the file saved in?

    EDIT: You can execute a macro from VB6 when you have the spreadsheet open. I have a demo VB6 Excel add I did to help another user. I just added a button to test running a macro and it worked quite nicely.

    Code:
    ExcelFileName = "C:\Documents and Settings\Administrator\My Documents\employee scheduling.xlsm"
    ' how VB starts Excel in the background
    Set objExcel = New Excel.Application
    Set objWorkbook = objExcel.Workbooks.Open(ExcelFileName)
    
    ' tell Excel to process the numbers
    objExcel.Run "'employee scheduling. xlsm' ! Module1. ProcessNumbers"
    
    Dim objWorksheet As Excel.Worksheet
    Set objWorksheet = objWorkbook.Worksheets("Sheet3")
    
    MsgBox "The process result is: " & objWorksheet. Cells (16, 2) .Value
    
    AllDone True
    The AllDone sub saves and closes the workbook (this is the rest of the GitHub https://github.com/jdelano0310/VB6ExcelProcessor)

    This gives you the best of both worlds, Excel can deal with the processing of the numbers for you, and you stay in VB6.

    EDIT2: forgot the screenshots
    Attached Images Attached Images   
    Last edited by jdelano; Nov 30th, 2023 at 05:20 AM.

  5. #5

    Thread Starter
    New Member
    Join Date
    Nov 2023
    Posts
    7

    Re: Copy range of cells (Excel) to textbox in vb6 (not vba userform) and vice versa

    Quote Originally Posted by jdelano View Post
    Ah, I see. There is much more to the Excel-berg then ;-) Which version of Excel is the file saved in?
    The version of Office installed on my system is Microsoft Office Professional Plus 2010 and my Excel file has .xlsx extension.

  6. #6
    Addicted Member
    Join Date
    Jul 2022
    Posts
    167

    Re: Copy range of cells (Excel) to textbox in vb6 (not vba userform) and vice versa

    See my edit from the previous post, just now.

  7. #7

    Thread Starter
    New Member
    Join Date
    Nov 2023
    Posts
    7

    Re: Copy range of cells (Excel) to textbox in vb6 (not vba userform) and vice versa

    I wanted to perform these steps to increase the processing speed. I have found a solution. Please check my new thread.

  8. #8
    Fanatic Member
    Join Date
    Mar 2023
    Posts
    724

    Re: Copy range of cells (Excel) to textbox in vb6 (not vba userform) and vice versa

    User still need to have Excel or Office scripting Library installed and not the "general" VBA scripting modules.

  9. #9
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,629

    Re: Copy range of cells (Excel) to textbox in vb6 (not vba userform) and vice versa

    Here's my take (having done extremely extensive Excel automation from VB6 in the past).

    If you use late bound (??? As Object) for all your Excel object variables, it won't matter which version of Excel you have installed on your computer.

    Often, when I'm doing something sort of "new" with respect to Excel automation, I'll often use the Excel macro recorder to get the job 1/2 done. Then, I'll copy-paste that code into VB6, and finish things up. Doing that, you do have to be aware that the Excel macro takes advantage of certain implicit "parent" objects that must be explicitly specified when brought into VB6. But after you do that a couple of times, it's no biggie.

    Beyond that, it's just learning how to use Excel's object library. I've got an old HLP file I use that helps me with that.

    ----------

    Also, another trick that others use (but I don't) is to use early-binding for VB6 Excel automation development, and then, once everything is done, re-declare all those Excel object variables to (As Object). Using that approach, you get the advantage of Intellisense during development. Also, if you use that approach, don't forget to un-reference the Excel library when you're done, as you won't need it if everything is late-bound.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  10. #10
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,725

    Re: Copy range of cells (Excel) to textbox in vb6 (not vba userform) and vice versa

    ado readll 100 rang cell datas
    and ado save data to xlsx file.

Posting Permissions

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



Click Here to Expand Forum to Full Width