-
Jan 26th, 2015, 04:55 PM
#1
Thread Starter
New Member
Problem with Excel in a VB.Net form
Hi everyone,
I am having an issue with a VB.Net application that opens Excel within a panel on my form. I am somewhat of a beginner at .Net, so this issue may be simple, although I have done a lot of searching and not found a solution.
Everything works fine when I run my application on an XP/Excel 2010 machine. I run into the issue when I run it on a Win7/Excel 2013 machine. I am using the Microsoft Excel 15.0 object library, though, so I think that should be good for Excel 2013.
My form has two buttons. One of the buttons opens an existing spreadsheet, and the other creates a new spreadsheet and adds some formatting. When running the application on the Win7/Excel2013 machine, I run in to issues creating a new spreadsheet (Incidentally, loading an existing spreadsheet works fine with no issues). The code will open a new spreadsheet, embed it in the panel, and complete the formatting. When this is done though, I cannot change cells in my spreadsheet. It is almost as if it is completely locked down.
When stepping through the code, I can tell that this behavior starts when the line of code sets the value of oSheet. Here is my code, after cutting out a lot of stuff not applicable to this problem.
Code:
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports Microsoft.Office.Core
Dim oExcel As New Excel.Application
Dim oWB As Excel.Workbook
Dim WithEvents oSheet As Excel.Worksheet
Private Sub btnNewConfig_Click(sender As Object, e As EventArgs) Handles btnNewConfig.Click
'This routine will create a new configuration file.
oExcel.DisplayAlerts = False
oExcel.Workbooks.Add()
oExcel.Application.WindowState = Excel.XlWindowState.xlMaximized
oExcel.Visible = True
'Maximize the Excel window within the panel
SetParent(oExcel.Hwnd, pnlExcel.Handle)
SendMessage(oExcel.Hwnd, WM_SYSCOMMAND, SC_MAXIMIZE, 0)
boolFormatExcel = True
oWB = oExcel.ActiveWorkbook
oSheet = oWB.Worksheets("Sheet1")
oSheet.Range("A1").Value = "Address"
oSheet.Range("A1").ColumnWidth = 14
oSheet.Range("B1").Value = "Description"
oSheet.Range("B1").ColumnWidth = 35
........
The thing that is strange to me is that when I open an existing spreadsheet, it still sets oWB and oSheet in the same way, without any issues. This is how I did it when loading an existing configuration, and this works properly...
Code:
Private Sub btnLoadConfig_Click(sender As Object, e As EventArgs) Handles btnLoadConfig.Click
'This routine will prompt for a configuration file and load it into the form.
'sExcelFileName = oExcel.GetOpenFilename(FileFilter:="Excel Files,*.xlsx")
dlgOpenFile.Title = "Open a Configuration File"
dlgOpenFile.Filter = "Excel Files|*.xlsx"
dlgOpenFile.FileName = ""
Dim DidWork As Integer = dlgOpenFile.ShowDialog()
sExcelFileName = dlgOpenFile.FileName
If DidWork <> DialogResult.Cancel Then
oExcel.DisplayAlerts = False
oExcel.Workbooks.Open(sExcelFileName)
oWB = oExcel.ActiveWorkbook
oSheet = oWB.Worksheets("Sheet1")
'Check if a valid configuration file is selected. If so, load it. If not, pop up a message to user.
If oSheet.Cells(1, 1).Value = "Address" Then
oExcel.Application.WindowState = Excel.XlWindowState.xlMaximized
oExcel.Visible = True
'Maximize the Excel window
SetParent(oExcel.Hwnd, pnlExcel.Handle)
SendMessage(oExcel.Hwnd, WM_SYSCOMMAND, SC_MAXIMIZE, 0)
..............
So, it seems to me like I am doing something that Excel 2013 does not like but I cannot seem to figure out what it is.
Thanks for your help...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|