Errors when Saving Excel file to CSV 1

[KellyStee]

I'm trying to convert an Excel file to CSV and this is the code I have so far:

Sub ConvertToCSV(ByVal strFileName As String, ByVal strNewFileName As String)
Dim objExcel, objExcel_Sheet As Object
Set objExcel = CreateObject("Excel.application&quot
objExcel.Workbooks.Open FileName:=strFileName
Set objExcel_Sheet = objExcel.ActiveSheet
objExcel.SaveAs FileName:=strNewFileName, FileFormat:=xlCSV, CreateBackup:=False
objExcel.Close True
objExcel.Quit
End Sub

I keep getting the following error though:
"Object doesn't support this property or method."

Can anyone help?

Thanks!
Kelly

 

[ADoozer]

a couple of things....

the line as follows:-

Dim objExcel, objExcel_Sheet As Object

your are actually declaring

dim objexcel as variant,objexcel as object

change this to

Dim objExcel as object, objExcel_Sheet As Object

however thats not the problem,

an excel application object does not support this methods!

objExcel.SaveAs FileName:=strNewFileName, FileFormat:=xlCSV, CreateBackup:=False

you can change this to

objExcel_Sheet.SaveAs FileName:=strNewFileName, FileFormat:=xlCSV, CreateBackup:=False

(if you need to have late binding, it may help to develop your app with a reference set to excel object library, this way you can use the object browser (F2) to see what methods are supported, but remember to uncheck it when your done!!)

good luck! If somethings hard to do, its not worth doing - Homer Simpson

 

[KellyStee]

ADoozer,
Thanks so much for the help! I've never really used the Object Library before (can you tell I'm a total newbie at this?!!?).
I've definitely progressed but I'm still stuck with one issue. This is my new code:

Sub ConvertToCSV(ByVal strFileName As String, ByVal strNewFileName As String)
Dim objExcel As Object
Dim objExcel_Sheet As Object
Set objExcel = CreateObject("Excel.application&quot
objExcel.Workbooks.Open FileName:=strFileName
Set objExcel_Sheet = objExcel.ActiveSheet
objExcel_Sheet.SaveAs FileName:=strNewFileName, FileFormat:=xlCSV, CreateBackup:=False
objExcel.Workbooks.Close
objExcel.Quit
Set objExcel_Sheet = Nothing
Set objExcel = Nothing
End Sub

The problem is that after the "saveas" line, the Close line pops up with a message box asking me if I want to save my changes to the .csv file before closing (it's weird to me that it's even asking that since I have really made no changes since the "saveas" line). I need to be able to get past that message box and I thought the way to do it was to put in this line of code: objExcel.Close ("No&quot
but I keep getting this error: "Wrong number of arguments or invalid property assignment"
I checked the object library and the "Close" command (I thought) takes 3 optional parameters.
What the heck am I doing wrong?

Thanks again!
Kelly

 

[ADoozer]

ok, here goes, object library

click project->references, a box will pop up, scroll down to microsoft excel 9.0 object library and tick the box (may be version 10 depending on what office you have installed)

now if you hit F2 the object browser window pops up, where it says all librarys at the top, click and select excel, in the class window is a list that will help you identify how to use the excel object library!!

also you can declare your object as

dim objExcel as excel.application

which then gives the IDE a ticket to use intellisense to help you make your calls!!

anyway enough of that for now, i have altered your code, it works on my machine, i hope it works for you also!

Sub ConvertToCSV(ByVal strFileName As String, ByVal strNewFileName As String)
  
  Dim objExcel As Object
  Dim objExcel_WorkBook As Object
  
  Set objExcel = CreateObject("Excel.application")
  
  Set objExcel_WorkBook = objExcel.Workbooks.Open(strFileName)
  
  objExcel.Visible = True
  
  objExcel_WorkBook.SaveAs strNewFileName, xlCSV, , , , False
  
  objExcel_WorkBook.Close False
  
  objExcel.Quit
  
  Set objExcel_WorkBook = Nothing
  Set objExcel = Nothing
  
End Sub

good luck!! If somethings hard to do, its not worth doing - Homer Simpson

 

[ADoozer]

oops forgot to remove the line, objExcel.visible=true If somethings hard to do, its not worth doing - Homer Simpson

 

[KellyStee]

ADoozer,
Success! Thanks so much for the help! And thanks for the lesson on the Object Library too! BTW, love the signature! )

Kelly