FileFormat := ????

Question

I am trying to open an existing file and do a Save as.
So here is a quick background. I have a bunch of files that look and feel like excel, but it turns out, they really are not. To make them truley excel files, I need to open and save as, specifically choosing the file type as excel.

Now, I'm a complete VB newbie and I'm not sure how much info you need on the rest of the code I have so far, but the line that I have a question on is

blahblah.SaveAs(Filename:="C:\myfile.xls", FileFormat:= ???)

The blahblah I have above is a variable of type Excel.Workbook. I'm hoping this is enough to tell you what I'm doing.

So, what is the ??? I need to tell it I want to save the file as an Excel file?
I'm thinking it's gotta be some constant or a number....

EDIT: If it matters, I'm on Excel 2003

Answer 1

Hello,

Hello - Here is what the SDK says about the FileFormat parameter:

FileFormat

Type: System..::.Object
The file format to use when you save the file. For a list of valid choices, see the FileFormat property. For an existing file, the default format is the last file format specified; for a new file, the default is the format of the version of Excel being used.

Based on this description, it seems that you would need to specify the type of file you want to save.

The following topic "Workbook.FileFormat Property"-http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.fileformat(VS.80).aspx shows an example of testing the file to see if it is a normal workbook. If it is a normal workbook, then the code uses "Excel.XlFileFormat.xlXMLSpreadsheet" for the FileFormat parameter.

Note that you can always use the macro recorder in Excel to capture the names of parameters. For example, Record a new macro (Tools/Macro/Record New Macro. Then save the file as whatever format you want.  Then go back and open the macro in the VBA editor and see what parameter was used.  You can then use that parameter in your code.

Norm E.

---

Norm Estabrook

Answer 2

  I recorded a new macro. Now, how do I open the macro so I can see what code it generated?

EDIT: I figured it out. It turns out the FileFormat is xlNormal.

Answer 3

Excellent.  One more note.  Sometimes it is hard to figure out how to express in C# or VB a parameter that was revealed by the macro recorder. For example the macro recorder might say xlXMLSpreadsheet.  However in code you might need to express it as as something like "Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet".

To discover how a parameter is expressed in C# or VB, you could use object browser ("View -> Object Browser") and then search for parameter name that the macro recorder revealed to you.  In the list of results that object browser returns, you can usually see the way in which the parameter is expressed (as an class, enumeration etc).

Norm E.

---

Norm Estabrook

Answer 4

Well, the macro recorder said the format is xlNormal. So that's what I wanted to save as.

So I tried expressing this in VB by saying ....Excel.XlFileFormat.blahblah, where blahblah is the format I want.

When I got to "XlFileFormat." part, I tried to look for "xlNormal", but it doesn't come up on intellisense. However, it has the example you gave, xlXMLSpreadsheet.

Also, while I was researching this, I stumbled on a post on a different forum that said to use

FileFormat:=-4143

I don't know what the number means, but it worked. I guess the number corresponds to a certain format type....

Answer 5

Looking at the reference topic for the XlFileFormat enumeration, it looks like there is an xlWorkbookNormal value that has the value -4143. I presume that xlWorkbookNormal corresponds to the xlNormal value that the macro recorder gave you.

McLean Schofield

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 6

McLean Schofield - MSFT said:

Looking at the reference topic for the XlFileFormat enumeration, it looks like there is an xlWorkbookNormal value that has the value -4143. I presume that xlWorkbookNormal corresponds to the xlNormal value that the macro recorder gave you.

McLean Schofield

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Ok, that explains why that worked. Thanks.