Visual basic 6 to excel application

I wish to open an excel application from Visual basic 6.

I can create and save using the following code:
Private Sub Form_Load()
Dim obExcelApp As Object
Dim obWorkSheet As Object
Set obExcelApp = CreateObject("excel.Application")
obExcelApp.Workbooks.Add
Set obWorkSheet = obExcelApp.ActiveSheet
obWorkSheet.Cells(1, 1).Value = "Date"
obWorkSheet.Cells(1, 2).Value = "Num"
obWorkSheet.Cells(2, 1).Value = 21923.44
obWorkSheet.Cells(2, 2).Value = "April"
obExcelApp.Save ("C:\Donations1\Register.XLS")
End Sub

I wish to open the worksheet and edit it on screen.
JAwilsonAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
etienne14Connect With a Mentor Commented:
If you really want to open and edit the worksheet try this

Shell "excel.exe c:\donations\register.xls",vbmaximizedfocus

regards
etienne14
0
 
bruintjeCommented:
Hi JAWilson, you want to show excel on the screen while editing in code ? then add one line with visible

Private Sub Form_Load()
Dim obExcelApp As Object
Dim obWorkSheet As Object
Set obExcelApp = CreateObject("excel.Application")
obExcelApp.Visible = True
obExcelApp.Workbooks.Add
Set obWorkSheet = obExcelApp.ActiveSheet
obWorkSheet.Cells(1, 1).Value = "Date"
obWorkSheet.Cells(1, 2).Value = "Num"
obWorkSheet.Cells(2, 1).Value = 21923.44
obWorkSheet.Cells(2, 2).Value = "April"
obExcelApp.Save ("C:\Donations1\Register.XLS")
End Sub

else let me know,

:O)Bruintje
0
 
LunchyCommented:
JAwilson, it is to your advantage to reject an answer immediately if it does not completely satisfy your requirements.

Lunchy
Friendly Neighbourhood Community Support Moderator
0
 
hslowCommented:
hi,
you can open and edit yr excel file as below:

Option Explicit
Public appWorld As Excel.Application
Public wbWorld As Excel.Workbook


Sub OpenExcelFile()
On Error Resume Next 'ignore errors
    Set appWorld = GetObject(, "Excel.Application")
'look for a running copy of Excel
    If Err.Number <> 0 Then 'If Excel is not running then
      Set appWorld = CreateObject("Excel.Application")
     'run it
    End If
    Err.Clear   ' Clear Err object in case error occurred.
   
    On Error GoTo 0 'Resume normal error processing

    Set wbWorld = appWorld.Workbooks.Open("C:\Donations1\Register.XLS")
End Sub

Private Sub Form_Load()
Dim obWorkSheet As Excel.Worksheet

OpenExcelFile()
appWorld .Visible = True
appWorld .Workbooks.Add
Set obWorkSheet = appWorld.ActiveSheet
obWorkSheet.Cells(1, 1).Value = "Date"
obWorkSheet.Cells(1, 2).Value = "Num"
obWorkSheet.Cells(2, 1).Value = 21923.44
obWorkSheet.Cells(2, 2).Value = "April"
obExcelApp.Save ("C:\Donations1\Register.XLS")

End Sub

Hope this can help you :-)
Regards,
hslow
0
 
JAwilsonAuthor Commented:
I used hslows comment to create the following code which works.
Private Sub Command5_Click()
Dim i As Integer
Dim j As Integer
Dim Numb1 As Integer
Dim Numb2 As Integer
Dim Numb3 As Integer
Dim obWorkSheet As Excel.Worksheet
OpenExcelFile
'obWorkSheet.Open ("c:\Donations1" & "\Register.XLS")
Set wbWorld = appWorld.Workbooks.Open("c:\Donations1" & "\Register1.XLS")
'Open("c:\donations1\register1.exl")
'OpenExcelFile
appWorld.Visible = True
'appWorld.Workbooks.Add
Set obWorkSheet = appWorld.ActiveSheet
obWorkSheet.Cells(1, 1).Value = "Date"
obWorkSheet.Cells(1, 2).Value = "Numb"
obWorkSheet.Cells(1, 3).Value = "DisbID"
'obWorkSheet.Cells(2, 1).Value = 9 / 11 / 1
obWorkSheet.Cells(1, 3).Value = "Account"
Dim DbDonations1 As New ADODB.Connection
Dim rsReceipts As New ADODB.Recordset
Dim rsDisbursements As New ADODB.Recordset
Dim rsTransfers As New ADODB.Recordset
Dim rsAssigned As New ADODB.Recordset
DbDonations1.Open "Donations1"
Set rsAssigned = rsReceipts
Data1.Recordset.MoveLast
Numb1 = Data1.Recordset("receiptID")
'MsgBox "Numb1= " & Numb1
For i = 1 To Numb1
Text3.Text = i
Set rsAssigned = DbDonations1.Execute("Select * From Receipts where receiptID = '" & Text3.Text & "'")
On Error GoTo Error_Fix
j = i + 1
obWorkSheet.Cells(j, 1).Value = rsAssigned("CreationDate"):
obWorkSheet.Cells(j, 2).Value = rsAssigned("AccountID"):
obWorkSheet.Cells(j, 3).Value = rsAssigned("Description")
Next
Set rsAssigned = rsDisbursements
Data3.Recordset.MoveLast
Numb2 = Data3.Recordset("DisbID")
'MsgBox "numb2 =" & Numb2
For i = 1 To Numb2
Text5.Text = i
Set rsAssigned = DbDonations1.Execute("Select * From Disbursements where DisbID = '" & Text5.Text & "'")
On Error GoTo Error_Fix
j = i + Numb1
obWorkSheet.Cells((j + 1), 1).Value = rsAssigned("CreateDate"):
obWorkSheet.Cells((j + 1), 2).Value = rsAssigned("AcctID"):
obWorkSheet.Cells((j + 1), 3).Value = rsAssigned("DisbID")
Next
Set rsAssigned = rsTransfers
Data4.Recordset.MoveLast
Numb3 = Data4.Recordset("TransferID")
'MsgBox "numb3 =" & Numb3
For i = 1 To Numb3
Text7.Text = i
Set rsAssigned = DbDonations1.Execute("Select * From Transfers where TransferID = '" & Text7.Text & "'")
On Error GoTo Error_Fix
j = i + (Numb1 + Numb2)
obWorkSheet.Cells((j + 1), 1).Value = rsAssigned("TransferDate"):
obWorkSheet.Cells((j + 1), 2).Value = rsAssigned("TransDescription"):
obWorkSheet.Cells((j + 1), 3).Value = rsAssigned("TransferID")
Next

Error_Fix:
Err.Clear
End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.