Link to home
Start Free TrialLog in
Avatar of sporenza
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.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:


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:

Open in new window

Screen.jpg
Avatar of Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs)
Flag of Australia image

Hi sporenza,

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.Add

Regards,

Wayne
Avatar of sporenza
sporenza

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

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.Excel

Thanks,!
sporenza,

You need to import Excel as well....

    Imports Excel = Microsoft.Office.Interop.Excel

...or you can do it without a reference to Excel at all, by using Late Binding....

    Dim excelApplication As Object = CreateObject("Excel.Application")
    Dim excelWorkbook As Object = excelApplication.Workbooks.Add()
 
...then open the text file as before.

Wayne
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)
OK, then. Leave the declaration as it was, but just assign a new workbook to it before trying to open the text file.
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!
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.Exce

....and declare the objects like this....

        Dim excelApplication As New XL.Application
        Dim excelWorkbook As XL.Workbook = excelApplication.Workbooks.Add()

Wayne
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!

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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs)
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
KOOLIO!!

THANKS MILLIONS