Microsoft.Office.Interop.Excel Help

[KodeKrazy]

I'm trying to read an Excel worksheet and do a find/replace for all of the commas "," in any of the cells in the sheet and replace them with a dash "-" I can get as far as getting the workbook opened but I'm not sure where to go from here. Is there a simple method to accomplish this?

Below is the code I have so far:

Dim exl As Microsoft.Office.Interop.Excel.Application
 
        Dim sht As Microsoft.Office.Interop.Excel.Worksheet
 
        exl = New Microsoft.Office.Interop.Excel.Application
 
        exl.Workbooks.Open("C:\...\WHOLESALEpl.xls")    ' Actual path omitted

[kadghar]

I'm trying to read an Excel worksheet and do a find/replace for all of the commas "," in any of the cells in the sheet and replace them with a dash "-" I can get as far as getting the workbook opened but I'm not sure where to go from here. Is there a simple method to accomplish this?

Below is the code I have so far:

Dim exl As Microsoft.Office.Interop.Excel.Application
 
        Dim sht As Microsoft.Office.Interop.Excel.Worksheet
 
        exl = New Microsoft.Office.Interop.Excel.Application
 
        exl.Workbooks.Open("C:\...\WHOLESALEpl.xls")    ' Actual path omitted

So far so good. Now, if you have doubts working with your excel's vba, just record a macro and copy-paste the code. For what you want, something like this might be of help:

Dim Obj1 as object
set obj1 = createobject("excel.application")
ob1.workbooks.open("c:\...\WHOLESALEpl.xls")
obj1.Cells.Replace What:=",", Replacement:="-", LookAt:=2, SearchOrder _
        :=1, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
obj1.visible=true

Now, just make sure to replace the xlconstants with its numeric value, since other VB than Excel's VBA wont recognize them. That's why, even when the macro recorder wrote LookAt:=xlPart, you should use LookAt:=2

HTH

[KodeKrazy]

You totally ROCK! I knew there had to be a relatively easy method due to the complexity of the excel.application object. Just wasn't sure how to access it. Your sample code worked like a charm! Thanks a Million!

KK

[KodeKrazy]

Ok, here is what I have now:

Dim exl As Microsoft.Office.Interop.Excel.Application
 
        Dim sht As Microsoft.Office.Interop.Excel.Worksheet
 
        exl = New Microsoft.Office.Interop.Excel.Application
 
        exl.Workbooks.Open("C:\...\WHOLESALEpl.xls")
 
        sht = exl.Workbooks(1).ActiveSheet
 
        exl.Cells.Replace(",", "-", 2, 1, False, False, False)
 
        sht.SaveAs("C:\...\WHOLESALEplfixed.csv", _
                   FileFormat:="xlCSV")
 
        exl.Application.Quit()

[kadghar]

Ok, here is what I have now:

Glad to be of help.

Seems good, is it allright?

Kad

[KodeKrazy]

It works great (does the replace in all cells) until I try to do the SaveAs method. It then returns an unhandled COMException error, specifically: "Exception from HRESULT: 0x800A03EC" I have tried most of the other supposed "fixes" that I was able to find online (e.g.: Setting "Trust VB Project") in the actual Excel application. Alas, none of them were effective in alleviating the error. Any help is greatly appreciated.

KK

[KodeKrazy]

Actually, I did change a couple of lines of code in other foiled attempts at removing the error. The code currently looks like this:

                      Try
 
        Dim exl As New Microsoft.Office.Interop.Excel.Application
 
        Dim xlwb As Microsoft.Office.Interop.Excel.Workbook
 
        'Dim sht As Microsoft.Office.Interop.Excel.Worksheet
 
        xlwb = exl.Workbooks.Open("C:\...\WHOLESALEpl.xls")
 
         exl.Cells.Replace(",", "-", 2, 1, False, False, False)
        exl.DefaultSaveFormat = Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV
        exl.ActiveWorkbook.SaveAs("C:\...\WHOLESALEplfixed.csv", _
        FileFormat:="xlCSV")
 
        exl.Application.Quit()
        Catch ex As Exception
        MsgBox(ex.Message)
 
        End Try

[KodeKrazy]

I finally got it to work!!! Here is the VB code:

       Try
            Dim exl As New Microsoft.Office.Interop.Excel.Application
 
            Dim xlwb As Microsoft.Office.Interop.Excel.Workbook
 
            xlwb = exl.Workbooks.Open("C:\...\WHOLESALEpl.xls")
 
            exl.Cells.Replace(",", "-", 2, 1, False, False, False)
            exl.DefaultSaveFormat = Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV
            exl.ActiveWorkbook.SaveAs("C:\...\WHOLESALEplfixed.csv", _
            6)
 
            exl.Application.Quit()
        Catch ex As Exception
            MsgBox(ex.Message)
 
        End Try

Note that I changed the File Format:= "xlCSV" to 6 (the value for xlCSV) did a "build" (since I am working in VS2008) instead of just a "debug". Ran it and it works like a charm!

Thanks again for your help & I hope this post can help another developer with a similar situation.

Cheers!

KK

[KodeKrazy]

One thing I noticed while debugging was that the EXCEL application was still running (according to Task Manager) until I stopped debugging. Is this normal behavior? Is there a way to make excel exit, to save system overhead, before the program exits completely? I thought that was handled by the Application.Quit method? Do I need to set it to Null or Nothing?

BTW & FTR - TSDN or Bytes or whatever we are calling it is the best forum on the web!!! I have posted on other forums and get the quickest, and most useful information from THIS site.

Thanks,

KK

[kadghar]

One thing I noticed while debugging was that the EXCEL application was still running (according to Task Manager) until I stopped debugging. Is this normal behavior? Is there a way to make excel exit, to save system overhead, before the program exits completely? I thought that was handled by the Application.Quit method? Do I need to set it to Null or Nothing?

That issue has been long discussed here. Search it in the site, you might find something. I'll check it out later too.

One quick way will be setting the application visible to true, and close it normaly

BTW & FTR - TSDN or Bytes or whatever we are calling it is the best forum on the web!!! I have posted on other forums and get the quickest, and most useful information from THIS site.

Thanks,

KK

^.^

Wow, those are nice words. Thank you.

[MeanDude]

As far as killing the instance of Excel in your processes, I found this kicking around another forum:

System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oExcel)

Explanation given was this: a COM assembly has a life of its own - freestanding from your code. This line is what instructs that object to die. I tested this fix, and it seemed to work for me.

Happy coding!

- MeanDude