By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,092 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

Microsoft.Office.Interop.Excel Help

KodeKrazy
P: 32
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:

Expand|Select|Wrap|Line Numbers
  1. Dim exl As Microsoft.Office.Interop.Excel.Application
  2.  
  3.         Dim sht As Microsoft.Office.Interop.Excel.Worksheet
  4.  
  5.         exl = New Microsoft.Office.Interop.Excel.Application
  6.  
  7.         exl.Workbooks.Open("C:\...\WHOLESALEpl.xls")    ' Actual path omitted
Apr 15 '08 #1
Share this Question
Share on Google+
10 Replies


kadghar
Expert 100+
P: 1,295
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:

Expand|Select|Wrap|Line Numbers
  1. Dim exl As Microsoft.Office.Interop.Excel.Application
  2.  
  3.         Dim sht As Microsoft.Office.Interop.Excel.Worksheet
  4.  
  5.         exl = New Microsoft.Office.Interop.Excel.Application
  6.  
  7.         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:

Expand|Select|Wrap|Line Numbers
  1. Dim Obj1 as object
  2. set obj1 = createobject("excel.application")
  3. ob1.workbooks.open("c:\...\WHOLESALEpl.xls")
  4. obj1.Cells.Replace What:=",", Replacement:="-", LookAt:=2, SearchOrder _
  5.         :=1, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
  6. 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
Apr 15 '08 #2

KodeKrazy
P: 32
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
Apr 15 '08 #3

KodeKrazy
P: 32
Ok, here is what I have now:
Expand|Select|Wrap|Line Numbers
  1. Dim exl As Microsoft.Office.Interop.Excel.Application
  2.  
  3.         Dim sht As Microsoft.Office.Interop.Excel.Worksheet
  4.  
  5.         exl = New Microsoft.Office.Interop.Excel.Application
  6.  
  7.         exl.Workbooks.Open("C:\...\WHOLESALEpl.xls")
  8.  
  9.         sht = exl.Workbooks(1).ActiveSheet
  10.  
  11.         exl.Cells.Replace(",", "-", 2, 1, False, False, False)
  12.  
  13.         sht.SaveAs("C:\...\WHOLESALEplfixed.csv", _
  14.                    FileFormat:="xlCSV")
  15.  
  16.         exl.Application.Quit()
Apr 15 '08 #4

kadghar
Expert 100+
P: 1,295
Ok, here is what I have now:
Glad to be of help.

Seems good, is it allright?

Kad
Apr 15 '08 #5

KodeKrazy
P: 32
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
Apr 15 '08 #6

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

Expand|Select|Wrap|Line Numbers
  1.                       Try
  2.  
  3.         Dim exl As New Microsoft.Office.Interop.Excel.Application
  4.  
  5.         Dim xlwb As Microsoft.Office.Interop.Excel.Workbook
  6.  
  7.         'Dim sht As Microsoft.Office.Interop.Excel.Worksheet
  8.  
  9.         xlwb = exl.Workbooks.Open("C:\...\WHOLESALEpl.xls")
  10.  
  11.          exl.Cells.Replace(",", "-", 2, 1, False, False, False)
  12.         exl.DefaultSaveFormat = Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV
  13.         exl.ActiveWorkbook.SaveAs("C:\...\WHOLESALEplfixed.csv", _
  14.         FileFormat:="xlCSV")
  15.  
  16.         exl.Application.Quit()
  17.         Catch ex As Exception
  18.         MsgBox(ex.Message)
  19.  
  20.         End Try
Apr 15 '08 #7

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

Expand|Select|Wrap|Line Numbers
  1.        Try
  2.             Dim exl As New Microsoft.Office.Interop.Excel.Application
  3.  
  4.             Dim xlwb As Microsoft.Office.Interop.Excel.Workbook
  5.  
  6.             xlwb = exl.Workbooks.Open("C:\...\WHOLESALEpl.xls")
  7.  
  8.             exl.Cells.Replace(",", "-", 2, 1, False, False, False)
  9.             exl.DefaultSaveFormat = Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV
  10.             exl.ActiveWorkbook.SaveAs("C:\...\WHOLESALEplfixed.csv", _
  11.             6)
  12.  
  13.             exl.Application.Quit()
  14.         Catch ex As Exception
  15.             MsgBox(ex.Message)
  16.  
  17.         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
Apr 15 '08 #8

KodeKrazy
P: 32
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
Apr 15 '08 #9

kadghar
Expert 100+
P: 1,295
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.
Apr 15 '08 #10

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

Expand|Select|Wrap|Line Numbers
  1. 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
Feb 8 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.