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 can 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
Calling Excel-contained subroutines from a form
Boo79
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.Worksheet On Error GoTo ErrorHandlerwsExcel2 = wbExcel.Sheets.Add(After:=wbExcel.Sheets(wbExcel.Sheets.Count))
"Working"wsExcel2.Name =
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
End SubwbExcel = wbWorkbook
wsExcel = wsWorksheet
Then with Start_Factor I call the form, so the Load-procedure is invoked.
Brians_224
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
cb3431
Hi Spotty,
Obviously something is wrong over here... I can't even get that to work...
With Imports Microsoft.Office.Interop I get a warning that either it is not a public namespace or it cannot be found.
If I simply leave that part out, I get an error when trying to add the workbook, so basically even before anything worthwhile has happened...
Richard
Prasenna
How To Automate Microsoft Excel from Visual Basic
http://support.microsoft.com/kb/219151/
This will get you going - it adds a sheet, sets some range properties... etc and changing some font properties such as bold.
sybaselu
Which version of office are you using and have you added the reference to the Excel class library.
This statement should work for 2003 or 2007 versions of office and you need to add the appropriate reference to the Excel class library.
Nisa
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
thomas woelfer
Spotty,
I'm using Excel 2003 and I've added the Excel 11.0 reference library. I also found the Office PIA's on the MS-site, which gave me the option to "import Microsoft.Office.Interop.Excel", but even after that I cannot add values to cells in Excel. I simply copied the example you sent me a link to earlier as-is into VB 2005, and it still failed
.
Richard