sporenza
asked on
Open Text File in Excel 2007 with VB 2008
When I build this code, I get an error on line "excelWorkBook = excelApplication.Workbooks "
I am attaching a screen shot as well as the snippet
Below is the code exception:
System.NullReferenceExcept ion was unhandled
Message="Object variable or With block variable not set."
Source="Microsoft.VisualBa sic"
StackTrace:
at Microsoft.VisualBasic.Comp ilerServic es.Symbols .Container ..ctor(Obj ect Instance) at Microsoft.VisualBasic.Comp ilerServic es.NewLate Binding.La teCall(Obj ect Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn) at Excel.Module1.Main() in C:\Documents and Settings\User\My Documents\Visual Studio 2008\Projects\Excel\Module 2.vb:line 15 at System.AppDomain._nExecute Assembly(A ssembly assembly, String[] args) at System.AppDomain.ExecuteAs sembly(Str ing assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.Hos tingProces s.HostProc .RunUsersA ssembly() at System.Threading.ThreadHel per.Thread Start_Cont ext(Object state) at System.Threading.Execution Context.Ru n(Executio nContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHel per.Thread Start()
InnerException:
THANKS!
I am attaching a screen shot as well as the snippet
Below is the code exception:
System.NullReferenceExcept
Message="Object variable or With block variable not set."
Source="Microsoft.VisualBa
StackTrace:
at Microsoft.VisualBasic.Comp
InnerException:
THANKS!
System.NullReferenceException was unhandled
Message="Object variable or With block variable not set."
Source="Microsoft.VisualBasic"
StackTrace:
at Microsoft.VisualBasic.CompilerServices.Symbols.Container..ctor(Object Instance) at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn) at Excel.Module1.Main() in C:\Documents and Settings\User\My Documents\Visual Studio 2008\Projects\Excel\Module2.vb:line 15 at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart()
InnerException:
Screen.jpg
ASKER
Wayne,
Thanks for the quick help, so earlier I did set the workbook to nothing...
when I change:
Dim excelWorkBook As Workbook = Nothing
to:
Dim excelWorkBook As Workbook = excelApplication.Workbooks .Add
I get: Public member 'OpenText' on type 'WorkbookClass' not found.
I am looking into it
Thanks for the quick help, so earlier I did set the workbook to nothing...
when I change:
Dim excelWorkBook As Workbook = Nothing
to:
Dim excelWorkBook As Workbook = excelApplication.Workbooks
I get: Public member 'OpenText' on type 'WorkbookClass' not found.
I am looking into it
ASKER
ok back that up...
I have
Dim excelWorkBook As Workbook = Nothing
you are suggesting:
Dim excelWorkbook As Excel.Workbook = excelApplication.Workbooks .Add
I get a 'Type Excel.Workbook' is not defined.. I am currently using Microsoft.Office.Interop.E xcel
Thanks,!
I have
Dim excelWorkBook As Workbook = Nothing
you are suggesting:
Dim excelWorkbook As Excel.Workbook = excelApplication.Workbooks
I get a 'Type Excel.Workbook' is not defined.. I am currently using Microsoft.Office.Interop.E
Thanks,!
sporenza,
You need to import Excel as well....
Imports Excel = Microsoft.Office.Interop.E xcel
...or you can do it without a reference to Excel at all, by using Late Binding....
Dim excelApplication As Object = CreateObject("Excel.Applic ation")
Dim excelWorkbook As Object = excelApplication.Workbooks .Add()
...then open the text file as before.
Wayne
You need to import Excel as well....
Imports Excel = Microsoft.Office.Interop.E
...or you can do it without a reference to Excel at all, by using Late Binding....
Dim excelApplication As Object = CreateObject("Excel.Applic
Dim excelWorkbook As Object = excelApplication.Workbooks
...then open the text file as before.
Wayne
ASKER
wayne,
let me appolgieze sorry i am a newbee
if I add imports Excel to the first line, I get an Imports alias 'Exce' conflicts with 'Namespace Excel' declared in the root namespace.
I think I would like to import Excel as you have instead of late binding, but not sure where the conflict is coming up...
I have referenced Excel 12.0 Object Library and have imported namespace of Microsoft.Office.Interpot. Excel... (not sure if this matters)
let me appolgieze sorry i am a newbee
if I add imports Excel to the first line, I get an Imports alias 'Exce' conflicts with 'Namespace Excel' declared in the root namespace.
I think I would like to import Excel as you have instead of late binding, but not sure where the conflict is coming up...
I have referenced Excel 12.0 Object Library and have imported namespace of Microsoft.Office.Interpot.
OK, then. Leave the declaration as it was, but just assign a new workbook to it before trying to open the text file.
ASKER
wayne,
i am sorry but I do not understand...
I took out the imports and changed everything back... I tried to add
excelWorkbook = excelApplication.Workbooks .Add before the open but I got the same error
, I tried a few other things but still no luck
thanks!
i am sorry but I do not understand...
I took out the imports and changed everything back... I tried to add
excelWorkbook = excelApplication.Workbooks
, I tried a few other things but still no luck
thanks!
Honestly, I would remove the reference to Excel and use late binding.
If you still wish to use Early Binding, import Excel like this....
Imports XL = Microsoft.Office.Interop.E xce
....and declare the objects like this....
Dim excelApplication As New XL.Application
Dim excelWorkbook As XL.Workbook = excelApplication.Workbooks .Add()
Wayne
If you still wish to use Early Binding, import Excel like this....
Imports XL = Microsoft.Office.Interop.E
....and declare the objects like this....
Dim excelApplication As New XL.Application
Dim excelWorkbook As XL.Workbook = excelApplication.Workbooks
Wayne
ASKER
ok, so I removed the reference to excel
And intialized it as stated and I am getting:
Public member 'OpenText' on type 'Workbook' not found.
I am attaching the lastest code..
thanks for your help!
And intialized it as stated and I am getting:
Public member 'OpenText' on type 'Workbook' not found.
I am attaching the lastest code..
thanks for your help!
Imports System.IO
Imports System.Console
Module Module1
Sub Main()
Dim excelApplication As Object = CreateObject("Excel.Application")
Dim excelWorkbook As Object = excelApplication.Workbooks.Add()
Dim paramWorkbookPath As String = "C:\MyJND1.jnd"
' Open the source workbook
excelWorkbook.OpenText(Filename:=paramWorkbookPath)
excelWorkbook.SaveAs("C:\NewExcel.xlsx")
' Close the Workbook object.
If Not excelWorkbook Is Nothing Then
excelWorkbook.Close(False)
excelWorkbook = Nothing
End If
' Close the ApplicationClass object.
If Not excelApplication Is Nothing Then
excelApplication.Quit()
excelApplication = Nothing
End If
GC.Collect()
GC.WaitForPendingFinalizers()
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
KOOLIO!!
THANKS MILLIONS
THANKS MILLIONS
Generally when working with Excel, the Excel Application object is dimensioned like this....
Dim excelApplication As New Excel.Application
But your error is caused by excelWorkbook not being set to anything. Try setting it to a blank workbook first, like this....
Dim excelWorkbook As Excel.Workbook = excelApplication.Workbooks
Regards,
Wayne