I have been pulling my hair out for days trying to get this code to work on my home laptop (64 bit running Windows 7, Excel 2010). I simply need to open an Excel file and access data from it, which is a piece of cake in VB6 and works fine in .NET on my work computer (32 bit, also running Windows 7, Excel 2003). I am referencing Microsoft Excel 14.0 and Office 14.0 object libraries.
Dim xls = New Microsoft.Office.Interop.Excel.Application
Dim xlsWbks As Microsoft.Office.Interop.Excel.Workbooks
Dim xlsWbk As Microsoft.Office.Interop.Excel.Workbook
Dim ListWks As Microsoft.Office.Interop.Excel.Worksheet
Dim DataWks As Microsoft.Office.Interop.Excel.Worksheet
Dim sht As Microsoft.Office.Interop.Excel.Worksheet
Dim listHeader As String
Dim dataHeader As String
xlsWbks = xls.Workbooks
xlsWbk = xlsWbks.Open("filename.xls", , True)
xls.Visible = True
For Each sht In xls.Worksheets
If sht.Name = "List" Then
ListWks = sht
Exit For
End If
Next
For Each sht In xls.Worksheets
If sht.Name = "Data" Then
DataWks = sht
Exit For
End If
Next
DataHeader = DataWks.Range("B1").Value
ListHeader = ListWks.Range("B1").Value
|
The first several times I tried running this code (stepping through with the debugger) it would run fine until the last line above, after which the code would simply hang, no error or status information. This entire code appears in the Load sub for my startup form and sometimes VB will reach the line and simply skip everything else in the subroutine and load the form on the screen; other times it just hangs in limbo.
I added the "xls.Visible = True" line and verified that the spreadsheet was indeed opening correctly and that the data was not in an invalid format. I even used the Immediate window and got valid data when assessing "ListWks.Range("B1").Value", but for some silly reason .NET won't proceed, even though it successfully executed a similar statement the line before. If I press pause nothing happens, except the play and pause buttons become inactive.
Just to make sure I wasn't crazy (because I have used Excel objects tons of times without issue at my work desktop) I saved the project and loaded it at work and it runs perfectly (it substituted the Excel/Office 14.0 objects with 11.0 to account for Office 2003). I tried it on my laptop again and fiddled with the references, trying older versions 9.0 and 5.0, to no avail. But when I set it back to 14.0, the code execution now freezes on the line where I open the spreadsheet xlsWbks.Open("filename.xls"), again with no error.
Any help would be appreciated; I do a lot of travelling and really need to get this to work on my laptop. I have had the same exact problem with Access DAO objects.