Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Mar 2004
    Location
    Knoxville, Tennessee, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inserting VB Data Into Excel (VB6)

    I need to insert data from a VB6 Textbox into a designated Cell on a specific page in a specific Excel Worksheet. Additionally, I would like to be able to insert the information into another designated Cell if the original targeted Cell contained any data. My guess is that I would need to open the specific Excel Worksheet from my VB Project and then instruct the procedure to insert the TextBox.text data into the designated Cell on the specific page of the Worksheet. I would greatly appreciate any help. The people on this site have been very wonderful to me! Thank you!

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting VB Data Into Excel (VB6)

    This is an adaptation of code found on VBForums.com, which I've used for other purposes, so the following is untested. It assumes rngTarget1 is the first choice to insert the value of Textbox1, and rngTarget2 is the alternative. I hope it works, or is at least a start.
    <code>
    Option Explicit

    <font color=448800>'Add reference to MS Excel xx.0 Object Library</font color=448800>
    Private moApp As Excel.Application

    Private Sub Command1_Click()
    Dim oWB As Excel.Workbook
    Dim rngTarget1 As Range
    Dim rngTarget2 As Range

    moApp.Visible = True
    Set oWB = moApp.Workbooks.Open("D:My DocumentsBook1.xls")

    <font color=448800>' For example:</font color=448800>
    Set rngTarget1 = oWB.Sheets("Sheet1").Cells(1, 2)
    Set rngTarget2 = oWB.Sheets("Sheet1").Cells(3, 4)

    If rngTarget1.Value <> "" Then
    rngTarget2.Value = Textbox1.Value
    Else
    rngTarget1.Value = Textbox1.Value
    End If

    Set oWB = Nothing

    End Sub

    Private Sub Form_Load()
    Set moApp = New Excel.Application
    End Sub
    </code>

    Looking at this again, "off the fly", it might be less messy to use the actual cell references in the IF block and avoid the range objects. e.g. oWB.Sheets("Sheet1").Range("A5").Value

    Alan

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Marietta, Georgia, USA
    Posts
    296
    Thanks
    9
    Thanked 4 Times in 4 Posts

    Re: Inserting VB Data Into Excel (VB6)

    Here is some sample code from an actual program:

    Public Sub GetXLitems(Index As Integer)

    <font color=448800>'************************************ **************************************
    'Purpose: Invoke Excel,
    ' call routines to load PIA spreadsheet
    ' and Factors spreadsheet
    'Inputs: N/A
    'Returns: N/A
    'Assumes: Excel is installed on computer where BenCalc runs
    'Effects: Invokes "extra" copy of Excel, alters various
    ' properties temporarily, then resets them
    '************************************************* ************************* </font color=448800>

    Dim loXL As Object

    <font color=448800>'Opens Excel </font color=448800>
    Set loXL = CreateObject("Excel.Application")
    loXL.Visible = True

    Call GetFactorsXL(Index, loXL)

    <font color=448800>'Now we can close the Excel application entirely </font color=448800>
    loXL.Quit
    Set loXL = Nothing

    End Sub


    Private Sub GetFactorsXL(Index As Integer, loXL As Object)

    <font color=448800>'************************************ **************************************
    'Purpose: Load Factors spreadsheet,
    ' return factor arrays to VB program
    'Inputs: N/A
    'Returns: N/A
    'Assumes: Excel invoked by calling routine
    'Effects: Sets value of various global arrays
    '************************************************* ************************* </font color=448800>

    Dim loXLwb As Object
    Dim lsPath As String
    Dim lvLSFac As Variant
    Dim liCol As Integer
    Dim liRow As Integer

    <font color=448800>'Loads workbook </font color=448800>
    Call FixAppPath(lsPath)
    Set loXLwb = loXL.Workbooks.Open (filename:=lsPath & "Factors2.xls")

    loXL.Sheets("LS factors").Select

    <font color=448800>'This sets up variant array as lvLSFac(48,4) RxC </font color=448800>
    lvLSFac = loXL.Range("B7:E54").Value

    For liRow = 23 To 70
    gfLSFac(liRow, 1) = CSng(lvLSFac(liRow - 22, 1))
    gfLSFac(liRow, 2) = CSng(lvLSFac(liRow - 22, 4))
    Next liRow

    <font color=448800>'Close current workbook, don't save changes </font color=448800>
    loXLwb.Close savechanges:=False
    Set loXLwb = Nothing

    End Sub


    Public Sub FixAppPath(asMyPath As String)

    <font color=448800>'************************************ **************************************
    'Purpose: Allow for handling of final backslash in App.Path
    ' If located in root, get the final backslash
    ' If not, don't get the final backslash
    'Inputs: Empty string variable asMyPath
    'Returns: Run-time directory with trailing backslash
    'Assumes:
    'Effects:
    '************************************************* *************************</font color=448800>

    asMyPath = App.Path
    If Not Right$(asMyPath, 1) = Chr$(92) Then
    asMyPath = asMyPath + Chr$(92)
    End If

    End Sub
    Rick Groszkiewicz
    Life is too short to drink bad wine (or bad coffee!)

  4. #4
    New Lounger
    Join Date
    Mar 2004
    Location
    Knoxville, Tennessee, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting VB Data Into Excel (VB6)

    Alan:

    Thank you very much for your help. Unfortunately, I cannot quite get it to work. I am sure it is me and not the Code. I am sure that it is obvious that I am not a computer programmer. I am an attorney trying to assist in a pro bono child support project. Perhaps I bit off more than I could comprehend in one setting with my initial question. If you do not mind I would like to simplify it and later build upon that Code after I get it to work. Thus, my revised question is:

    I would like to merely take textbox data found in Text1.Text from a VB6 form and insert it into cell C3 contained on Sheet1 of Book1 in Excel (found at C:Book1.xls). Any help you can give would be appreciated more than you can imagine. Thank you! Bob

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inserting VB Data Into Excel (VB6)

    First, select Project | References, and tick the check box for Microsoft Excel n.0 Object Library. The value of n depends on the version of Microsoft Office installed on your PC (Excel 97 = 8, Excel 2000 = 9, Excel 2002 = 10 and Excel 2003 = 11).

    Say that you have a command button cmdExport on the form containing the text box Text1. The On Click event procedure for this command button could look like this:

    Private Sub cmdExport_Click()
    Dim objXl As New Excel.Application
    Dim objWb As Excel.Workbook

    On Error GoTo ErrHandler

    Set objWb = objXl.Workbooks.Open("C:Book1.xls")
    objWb.Worksheets("Sheet1").Range("C3") = Me.Text1.Text
    objWb.Close SaveChanges:=True

    ExitHandler:
    On Error Resume Next
    Set objWb = Nothing
    objXl.Quit
    Set objXl = Nothing
    Exit sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  6. #6
    New Lounger
    Join Date
    Mar 2004
    Location
    Knoxville, Tennessee, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting VB Data Into Excel (VB6)

    It worked perfectly Hans! Thanks Again!

    Bob

Posting Permissions

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