Calling Excel-contained subroutines from a form
Hi all,
Could you please help me with the following problem?
I am trying to add a worksheet to the active Excel workbook and then format the first cell (A1) with a bold and somewhat larger font. For some weird reason, I can't even get the first thing to work as it should.
This is the code I've written for it:
Public Sub Add_Sheet(sName as string)
With ActiveWorkbook
.Sheets.Add after:=.Worksheets(.Worksheets.Count)
MsgBox "TEST1"
With .ActiveSheet
.Name = sName
MsgBox "TEST2"
With .Cells(1, 1).Font
.Name = "Arial"
.Size = 14
.Bold = True
End With
MsgBox "TEST3"
End With
End With
End Sub
If I put this inside a VBA Macro, it works just fine. I see three messages stating TEST1, TEST2 and TEST3. However, I need to call this subroutine from a form I've created using regular VB 2005.
So basically what I'm doing is the following. I call the form (it's a dll) from within Excel. On that form a button resides that calls back to the above subroutine in the Excel / VBA macro providing it with the required name of the worksheet.
What I cán do, is have Excel write everything I want on the current ActiveSheet. I can also get it to return all the names of all the current worksheets. So the very 'basics' of Excel still work. However, when I try to simply add a worksheet to the active workbook (doesn't sound very advanced either...), Excel is unable to comply. When I try to add a worksheet, I simply get the following error:
Method 'Add' of object 'Sheets' failed.
The same kind of thing happens when I remove the 'adding' of the worksheet and simply try to change the font of the very first cell:
Method 'Font' of object 'Range' failed.
Writing whatever I like on the active sheet is no problem, changing the name of the sheet or getting all the names of the active sheets neither is, but heaven forbid if I try to do something naughty like adding a worksheet or changing the font of a cell...
Has anyone ever had this kind of strange behaviour and know of a way to solve it?
Thanks in advance for your time,
Richard Meijer
Hi Spotty,
Thanks for the link. It gets me going for quite a bit, except that now I seem to be able to do just about anything 'advanced' in Excel, but I can't put values in cells anymore...
Here's the offending piece of my code. I've added the code how I tried to call the form and provide it with the Excel nuts and bolts a little further below, just for reference.
Private Sub Load_Form(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim wsExcel2 As Excel.WorksheetOn Error GoTo ErrorHandlerwsExcel2 = wbExcel.Sheets.Add(After:=wbExcel.Sheets(wbExcel.Sheets.Count))
wsExcel2.Name =
"Working"
wsExcel2.Range("a1", "d1").Font.Bold = True
MsgBox("Add value")
wsExcel2.Cells(1, 1).Value = "I'm a value"
MsgBox("Worked!")End Sub
The first few things work just fine. I get a new sheet named "Working" with the first four cells on the first row bold. However, with the last thing (adding the value) I get the ever clarifying error
1004 Excel from HRESULT: 0x800A03EC
In the example you sent me to though, the syntax looks (to me anyway) exactly identical. wsExcel2 is identical to the variabel oSheet in the example and the rest seems the same as well. Do you have any idea what I'm doing wrong?
Thanks in advance for your time.
Richard
For reference purposes, the code I've used to provide the form with a reference to Excel.
First the VBA-part, where I call the form and assign the Excel Application, Workbook and Worksheet to the Form:
Sub MainProgram()
Dim MyObject As Object 'Call the Program
Set MyObject = CreateObject("Factoren.FactorClass")
'Provide Excel, the workbook and the worksheet to the program
MyObject.FromExcel Excel.Application, Excel.ActiveWorkbook, Excel.ActiveSheet
'Start Program
Call MyObject.Start_Factor("Excel")
End Sub
The Sub "FromExcel" is called only when the form is invoked through Excel. It provides the form with the Excel bits and pieces:
Public Sub FromExcel(ByRef prgProgram As Excel.Application, ByRef wbWorkbook As Excel.Workbook, _ByRef wsWorksheet As Excel.Worksheet) objExcel = prgProgram
wbExcel = wbWorkbook
wsExcel = wsWorksheet
End SubThen with Start_Factor I call the form, so the Load-procedure is invoked.
The following is some code which although slighty different will add the second sheet, change the name and add values to the sheet. I'd put a breakpoint and check that the workbook and sheet are set and contain something prior to you trying to use them.
Imports Microsoft.Office.Interop
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
'On Error GoTo Err_Handler
' Start Excel and get Application object.
oXL = CreateObject("Excel.Application")
oXL.Visible = True
' Get a new workbook.
oWB = oXL.Workbooks.Add
oSheet = oWB.ActiveSheet
' Add table headers going cell by cell.
oSheet.Cells(1, 1).Value = "First Name"
oSheet.Cells(1, 2).Value = "Last Name"
oSheet.Cells(1, 3).Value = "Full Name"
oSheet.Cells(1, 4).Value = "Salary"
'//Create a new sheet and add items to it.
oWB.Sheets.Add(After:=oWB.Sheets(oWB.Sheets.Count))
oSheet = oWB.Sheets(oWB.Sheets.Count)
oSheet.Name = "Working"
oSheet.Cells(1, 1).Value = "Test1"
oSheet.Cells(2, 1).Value = "Test2"
oSheet.Cells(3, 1).Value = "Test3"
oSheet.Cells(4, 1).Value = "Test4"
' Make sure Excel is visible and give the user control
' of Microsoft Excel's lifetime.
oXL.Visible = True
oXL.UserControl = True
' Make sure you release object references.
oRng = Nothing
oSheet = Nothing
oWB = Nothing
oXL = Nothing
Catch ex As Exception
MsgBox(Err.Description, vbCritical, "Error: " & Err.Number)
End Try
End Sub
End Class
If you use the line
Imports Microsoft.Office.Interop.Excel
as you have said then lines like the following will fail.
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
This is because the imports will shorten down allow you to not have to fully qualify the names spaces.
The type name for and excel application is
Microsoft.Office.Interop.Excel.Application
So
Imports Microsoft.Office.Interop
will allow the use of Dim oXL As Excel.Application
But
Imports Microsoft.Office.Interop.Excel
will not allow the use of Dim oXL As Excel.Application
I'm using Excel 2007 on VB express but have used 2003 with the same syntax just a different Class Library referenced.
You can simply fully qualify all the types, such as
dim oXL as Microsoft.Office.Interop.Excel.Application
.