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
sporenzaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne Taylor (webtubbs)Commented:
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
0
sporenzaAuthor Commented:
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

0
sporenzaAuthor Commented:
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,!
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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

0
Wayne Taylor (webtubbs)Commented:
Sorry, my mistake. OpenText is a method of Workbooks, not Workbook. Try this instead....

     excelApplication.Workbooks.OpenText(Filename:=paramWorkbookPath)
     excelWorkbook = excelApplication.ActiveWorkbook
     excelWorkbook.SaveAs("C:\NewExcel.xlsx")

Wayne
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sporenzaAuthor Commented:
KOOLIO!!

THANKS MILLIONS
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.