Not able to save and open as .xlsx/Issue with formatting a text tab delimited format

[prajakta]

Hi Folks,

I am dynamically generating and naming an xl file based on some conditions. The use the following code-

Dim newFile As Long
newFile = FreeFile()
If FileName <> "" Then
Open "C:\imacro_files\" & FileName & ".xls" For Binary Access Write Lock Write As newFile
Close newFile

Application.Workbooks.Open FileName:="C:\vba_files\" & FileName & ".xls"

The problem here is if I try to save and open it as xlsx it gives an error like- Excel cannot open the file because the file format or file extension is not valid. Make sure file extension matches the format of the file.

Next, if I go ahead with using .xls and then format a column to have zeros appended to it the formatting just doesnt work. I get an error like- The file is Text (tab delimited file) and so it cant support the formatting. Now I not sure how do I handle this as I absolutely need to format the column. I tried using all kinds of functions and also storing it as a string but nothing works. All formatting is just removed and at the same time it doesn't even let me open it if in .xlsx format (as stated initially).

So, basically it would help if I could get either problem solved-
1. Be able to save and open the file as .xlsx
2. Be able to format the text tab delimited file or prevent the file from getting saved as text tab delimited
3. Any other workaround?

PS: The funny thing is though I get an error about the file being text tab delimited it still shows the format as .xls (Microsoft Office 97/2003)
Also, it might help to know that in the orgin file I am importing data from an xml file. From this origin file data is then transferred to this new dynamic file.
xml -> xlsm --> xls
Any inputs will be much appreciated.

Thanks
Prajakta

 

[pbornemeier]

This link may be of some assistance:
http://www.rondebruin.nl/saveas.htm

 

[prajakta]

That link helped for one part of the problem or atleast to help me find a work around.. Many Thanks pbornemeier!!