MAcro or BAtch file to change cell format

[jbalbo]

Hi
I receive a file from SQL where all the columns are formatted as "General" I would like to write a batch file to change them all to Text
I found this bs file that changes the font, but what would I use to change the format?
Thanks
'launch Excel and open file
Set xlObj = CreateObject("Excel.Application")
Set xlFile = xlObj.WorkBooks.Open("c:\temp\filename.xls")
'turn off screen alerts
xlObj.Application.DisplayAlerts = False
'loop through sheets
For Each Worksheet In xlFile.Worksheets
'change sheet to desired worksheet name
If Worksheet.Name = "SheetToRename" Then
Worksheet.Name = "NewName"
End If
'change all sheets to desired font
With Worksheet.Cells.Font
.Name = "Verdana"
.Size = 12
End With
Next
'save, close, then quit
xlFile.Close True
xlObj.Quit

 

[GWteB]

Try ...

Set xlObj = CreateObject("Excel.Application")
Set xlFile = xlObj.Workbooks.Open("c:\temp\filename.xls")
'turn off screen alerts
xlObj.Application.DisplayAlerts = False
'loop through sheets
For Each Worksheet In xlFile.Worksheets
'change sheet to desired worksheet name
If Worksheet.Name = "SheetToRename" Then
Worksheet.Name = "NewName"
End If
'change all sheets to desired font
With Worksheet.Cells.Font
.Name = "Verdana"
.Size = 12
.NumberFormat = "@"
End With
Next
'save, close, then quit
xlFile.Close True
xlObj.Quit

 

[jbalbo]

Thanks... Let me give it a try...
Sorry, for the dumb questions.. but would I run this like a batch file?

 

[GWteB]

No, actually it's written as a VB Script file, so if you're on Windows (would suggest updating your profile ...) it should run from File Explorer, provided it has a .VBS extension name.

 

[jbalbo]

Thanks for getting back, that didn't work, but maybe if II tell you what I really need, it might help
Sorry, not too good at this.
I have a file cald filename.xlsx it comes to me with the number format as "General", I need the script to open the Excel file, change the Number format to "Text", save and close

 

[GWteB]

Sorry, didn't look closely and made a mistake. The red colored line in my previous post should have looked like this:

.Parent.NumberFormat = "@"

 

[jbalbo]

Thank You GWteB....... Work Perfectly

 

[GWteB]

You are welcome & thanks for letting me know.