Issue with saving a file as .xlsx/formatting a xls or tab delimited file

prajakta

New Member
Joined
Mar 30, 2011
Messages
6
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Forum statistics

Threads
1,224,550
Messages
6,179,461
Members
452,915
Latest member
hannnahheileen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top