Inserting VB Data Into Excel (VB6)

Topic

New Reply

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!

Reply | Quote

Replies

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.

Option Explicit

'Add reference to MS Excel xx.0 Object Library
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")

' For example:
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


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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

It worked perfectly Hans! Thanks Again!

Bob

Reply | Quote

Here is some sample code from an actual program:

Public Sub GetXLitems(Index As Integer)

‘**************************************************************************
‘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
‘**************************************************************************

Dim loXL As Object

‘Opens Excel
Set loXL = CreateObject(“Excel.Application”)
loXL.Visible = True

Call GetFactorsXL(Index, loXL)

‘Now we can close the Excel application entirely
loXL.Quit
Set loXL = Nothing

End Sub

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

‘**************************************************************************
‘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
‘**************************************************************************

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

‘Loads workbook
Call FixAppPath(lsPath)
Set loXLwb = loXL.Workbooks.Open (filename:=lsPath & “Factors2.xls”)

loXL.Sheets(“LS factors”).Select

‘This sets up variant array as lvLSFac(48,4) RxC
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

‘Close current workbook, don’t save changes
loXLwb.Close savechanges:=False
Set loXLwb = Nothing

End Sub

Public Sub FixAppPath(asMyPath As String)

‘**************************************************************************
‘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:
‘**************************************************************************

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

End Sub

Reply | Quote