Solved: Macro to change file format.

[Zemekes]

Backround info : I have a set of approx. 100 files that are in tab delimited format. Using Excel 2003, i have a set of macros that i run to quickly format all 100 files to look & be sorted in the way that I need them. The files are located in 3 seperate directories and each are specifiaclly named (file location & names can not be change due to really stupid company rules.) After I run all my macros I must save the files in as a standard microsoft Excel Workbook.

Question: Does anyone know if there is a way to make a macro that will save the active workbook in the "Microsoft Office Excel Workbook" format without changing the file name or directory?

I have tried putting the below code at the end of my macro.
If ActiveWorkbook.FileFormat = xlText Then
ActiveWorkbook.SaveAs fileFormat:=xlNormal
End If
ActiveWorkbook.Save
ActiveWorkbook.Close

When I have this added, it seems to save all the changes but does not change the file format.

I have also tried this to no avail:

ActiveWorkbook.SaveAs fileFormat:=xlNormal
ActiveWorkbook.Save
ActiveWorkbook.Close

Can anyone help?

[Apps]

Hi

have you tried SaveAs filename-minus-filetype-extension & ".xls" ?

[Apps]

Apologies - if this works then you can also use the below function I used to use a lot :

[vba]Function StripFileName(xName As String) As String

Dim xLen As Integer, xStep As Integer
xLen = Len(xName)
For i = xLen To 1 Step -1
If Mid(xName, i, 1) = "." Then Exit For
Next i
StripFileName = Left(xName, i - 1)
End Function[/vba]
so you would have :

[vba]ActiveWorkbook.SaveAs StripFileName(#yourfilename#) & ".xls"[/vba]
Let us know if this works, as it would be good to know

[Kenneth Hobs]

Changing a text file to some other format means that it won't be a text file anymore.

If you are keeping the same filename which may not be indicative of the actual file type, windows will still associate the file to the application based on the file extension, not file type.

In any case, here is an example to do what I think you wanted.
[vba]Sub SaveAsXLS()
Dim wb As Workbook, strWB As String
strWB = "x:\csv.csv"
Set wb = Workbooks.Open(strWB)
MsgBox wb.FileFormat
Application.DisplayAlerts = False
wb.SaveAs strWB, xlNormal
Application.DisplayAlerts = True
MsgBox wb.FileFormat
wb.Close False
End Sub[/vba]

[Zemekes]

One thing I think i got mixed up. I believe that the files are actually CSV files that are saved with ".xls" at the end so they open in excel by default. however when trying to manually save one after making changes, i get the message"
"File.xls may contain features that are not compatible with Text (Tab delimited). Do you want to keep the workbook in this format?"

not sure if this little bit makes a difference in determining the VBA code or not.

Thanks for the replys =) I will test those out ASAP and let you know the results.

[Zemekes]

@ Apps
I'm not sure exactly what you meant. (I'm a Noob to reading & Writing VBA)

@ Kenneth Hobbs
When i added that code i recieved a run-time error '1004':
'x:\csv.csv' could not be found.

Debug has line in red highlighted.
[vba] Dim wb As Workbook, strWB As String
strWB = "x:\csv.csv"
Set wb = Workbooks.Open(strWB)
MsgBox wb.FileFormat
Application.DisplayAlerts = False
wb.SaveAs strWB, xlNormal
Application.DisplayAlerts = True
MsgBox wb.FileFormat
wb.Close False [/vba]

[Kenneth Hobs]

You need to change line before it. The csv.csv is an example. Put the path and name for the file that you want to open.

You can hard code the file to open as I did or use one of the various dialog methods to prompt the user for the file to open.

[Zemekes]

Is there a way to make a macro to make the change in the active workbook without hardcoding a specific filename & directory? I get 100 new files every month. Each of the files are uniquely named & located in 1 of 3 directories (depending on what building the data is from). Hard coding a file name wouldn't work as i only have to make the change to each specific file once.

Is there a way to code it so that it will work so that it will change the format of the active workbook regardless of the file's name or what directory it is in?

[Kenneth Hobs]

Sure.

You could put the macro in your Personal.xls and play it. You can comment out the MsgBox lines or delete them when you are happy with the macro.

e.g.
[VBA]Sub MakeThisWBNormal()
With ActiveWorkbook
MsgBox .FileFormat, , .FullName
Application.DisplayAlerts = False
.SaveAs .FullName, xlNormal
Application.DisplayAlerts = True
MsgBox .FileFormat, , .FullName
.Close False
End With
End Sub[/VBA]


If you don't have a Personal.xls, just record a dummy macro. You can then edit it's Module1 and add your Sub like that above.

[Zemekes]

Sweet that code worked nicely. I added it at the end of my formatting macro in PERSONAL.XLS It saved the files in the needed format while keeping the names and file locations the same.

When I run it though, I now get two error messages. They both have no text in the error message, just numbers.
"-4158"
"-4143"

Any ideas how to get get rid of those? They aren't major issues as i can click the OK button to & the macro continues to run perfectly fine. But not having them would make this just that much better =)

[Kenneth Hobs]

You can comment out the MsgBox lines or delete them when you are happy with the macro.

[Zemekes]

WOOT!!

That did the trick perfectly. Thanks a ton for the help =)