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
: 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] :
Comments
:
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]
: :
:
:
: 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]
: