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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Wayne Taylor (webtubbs)Connect With a Mentor AstronautCommented:
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
 
Wayne Taylor (webtubbs)AstronautCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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

THANKS MILLIONS
0
All Courses

From novice to tech pro — start learning today.