emporting data from excel into access using vb6.0

I've had to dump some records into excel to use the TREND function. Now I would like to bring these rows (records) that have the trends calculated back into an access database using visual basic 6.0 code

Comments

  • : I've had to dump some records into excel to use the TREND function. Now I would like to bring these rows (records) that have the trends calculated back into an access database using visual basic 6.0 code
    :


    automation will allow you to pull back specific cells-
    for this this example add 5 command buttons, 1 textbox, and the "Excel.xls" file saved to the vb6 project folder -

    [code]
    Option Explicit

    'VB6 MENU - PROJECT , REFERENCES, set a refernce to:
    'Microsoft Excel 10.0 Object Library


    Const cMyExcel = "Excel.xls"
    Dim cPath As String

    Private mvarExcel As Excel.Application
    Private Workbook As Object


    Private Sub Command1_Click()
    'SHOW EXCEL AND OPEN A FILE
    'workbook.Activate
    mvarExcel.Visible = True 'SHOW EXCEL
    mvarExcel.Workbooks.Open cPath & cMyExcel 'OPEN A FILE
    End Sub

    Private Sub Command2_Click()
    'NEW EXCEL WORKBOOK
    Dim row As Integer
    Dim wb As Object
    Dim ws As Worksheet

    mvarExcel.Visible = True
    mvarExcel.Workbooks.Add
    Set wb = mvarExcel.Application.ActiveWorkbook
    Set ws = wb.ActiveSheet

    'PUT VALUE IN TEXTBOX INTO EXCEL CELLS
    For row = 1 To 5
    ws.Rows.Cells(row, 1) = Text1.Text
    Next
    End Sub

    Private Sub Command3_Click()
    'GET VALUES FROM CELLS
    Dim row As Integer
    Dim wb As Object
    Dim ws As Worksheet

    Set wb = mvarExcel.Application.ActiveWorkbook
    Set ws = wb.ActiveSheet

    'DISPLAY VALUES IN EXCEL CELLS
    For row = 1 To 5
    MsgBox ws.Rows.Cells(row, 1)
    Next
    End Sub

    Private Sub Command4_Click()
    'PUT VALUES INTO CELLS
    Dim row As Integer
    Dim wb As Object
    Dim ws As Worksheet

    Set wb = mvarExcel.Application.ActiveWorkbook
    Set ws = wb.ActiveSheet

    'PUT TEXTBOX VALUES INTO EXCEL CELLS
    For row = 1 To 5
    ws.Rows.Cells(row, 1) = Me.Text1 + " " & row
    Next
    End Sub

    Private Sub Command5_Click()
    'SAVE WORKBOOK
    mvarExcel.Application.ActiveWorkbook.Save
    'mvarExcel.Application.ActiveWorkbook.SaveAs cPath & cMyExcel
    End Sub



    Private Sub Form_Load()
    'INIT EXCEL OBJECT
    Set mvarExcel = New Excel.Application
    cPath = App.Path + "" 'MyPath
    End Sub

    Private Sub Form_Unload(Cancel As Integer)
    'QUIT EXCEL
    mvarExcel.Quit
    Set mvarExcel = Nothing
    End Sub
    [/code]
  • : : I've had to dump some records into excel to use the TREND function. Now I would like to bring these rows (records) that have the trends calculated back into an access database using visual basic 6.0 code
    : :
    :
    :
    : automation will allow you to pull back specific cells-
    : for this this example add 5 command buttons, 1 textbox, and the "Excel.xls" file saved to the vb6 project folder -
    :
    : [code]
    : Option Explicit
    :
    : 'VB6 MENU - PROJECT , REFERENCES, set a refernce to:
    : 'Microsoft Excel 10.0 Object Library
    :
    :
    : Const cMyExcel = "Excel.xls"
    : Dim cPath As String
    :
    : Private mvarExcel As Excel.Application
    : Private Workbook As Object
    :
    :
    : Private Sub Command1_Click()
    : 'SHOW EXCEL AND OPEN A FILE
    : 'workbook.Activate
    : mvarExcel.Visible = True 'SHOW EXCEL
    : mvarExcel.Workbooks.Open cPath & cMyExcel 'OPEN A FILE
    : End Sub
    :
    : Private Sub Command2_Click()
    : 'NEW EXCEL WORKBOOK
    : Dim row As Integer
    : Dim wb As Object
    : Dim ws As Worksheet
    :
    : mvarExcel.Visible = True
    : mvarExcel.Workbooks.Add
    : Set wb = mvarExcel.Application.ActiveWorkbook
    : Set ws = wb.ActiveSheet
    :
    : 'PUT VALUE IN TEXTBOX INTO EXCEL CELLS
    : For row = 1 To 5
    : ws.Rows.Cells(row, 1) = Text1.Text
    : Next
    : End Sub
    :
    : Private Sub Command3_Click()
    : 'GET VALUES FROM CELLS
    : Dim row As Integer
    : Dim wb As Object
    : Dim ws As Worksheet
    :
    : Set wb = mvarExcel.Application.ActiveWorkbook
    : Set ws = wb.ActiveSheet
    :
    : 'DISPLAY VALUES IN EXCEL CELLS
    : For row = 1 To 5
    : MsgBox ws.Rows.Cells(row, 1)
    : Next
    : End Sub
    :
    : Private Sub Command4_Click()
    : 'PUT VALUES INTO CELLS
    : Dim row As Integer
    : Dim wb As Object
    : Dim ws As Worksheet
    :
    : Set wb = mvarExcel.Application.ActiveWorkbook
    : Set ws = wb.ActiveSheet
    :
    : 'PUT TEXTBOX VALUES INTO EXCEL CELLS
    : For row = 1 To 5
    : ws.Rows.Cells(row, 1) = Me.Text1 + " " & row
    : Next
    : End Sub
    :
    : Private Sub Command5_Click()
    : 'SAVE WORKBOOK
    : mvarExcel.Application.ActiveWorkbook.Save
    : 'mvarExcel.Application.ActiveWorkbook.SaveAs cPath & cMyExcel
    : End Sub
    :
    :
    :
    : Private Sub Form_Load()
    : 'INIT EXCEL OBJECT
    : Set mvarExcel = New Excel.Application
    : cPath = App.Path + "" 'MyPath
    : End Sub
    :
    : Private Sub Form_Unload(Cancel As Integer)
    : 'QUIT EXCEL
    : mvarExcel.Quit
    : Set mvarExcel = Nothing
    : End Sub
    : [/code]
    :

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories