VBA Routine to alter Spreadsheet Cell Formats
A while ago I needed a routine to alter the cell formatting of a client spreadsheet to importing. An EE expert provide the code to do that. This is the code that is passed the spreadsheet name and location:
I now need to do something very similar but in this case I want to format the cells on the spreadsheet as 'General', they are currently formatted as 'Date'.
I know the routine above works but I'm not sure how so I don't know how to revise it to accomplish what I need: Here's what I would like it to do:
If possible, starting on Row 6 of the spreadsheet (the first five rows are general info, the data starts on rows 6) for columns A - M, revise the column format to 'General'
Private Sub fixSpreadsheetNumFormat(passedNameAndLoc As String)
Dim xlApp As Object, xlWb As Object, xlWs As Object
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open(passedNameAndLoc)
Set xlWs = xlWb.Worksheets(1)
xlWs.Range("F:P").NumberFormat = "0"
'repeat as needed
With xlWb
.Save
.Close
End With
Set xlWs = Nothing
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing
End SubI now need to do something very similar but in this case I want to format the cells on the spreadsheet as 'General', they are currently formatted as 'Date'.
I know the routine above works but I'm not sure how so I don't know how to revise it to accomplish what I need: Here's what I would like it to do:
If possible, starting on Row 6 of the spreadsheet (the first five rows are general info, the data starts on rows 6) for columns A - M, revise the column format to 'General'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You should also consider going into Excel, recording a macro which accomplishes exactly what you want to do, then review the VBA code that the macro creates. In some instances it will be a bit cumbersome, but will get you in the general vicinity you need to be in to do this yourself, so you don't have to wait for one of us to respond.
The only thing I've found annoying is that the VBA generated by Excel will frequently result in a lot of:
.Select
With Selection
End With
Syntax, which you generally don't need.
The only thing I've found annoying is that the VBA generated by Excel will frequently result in a lot of:
.Select
With Selection
End With
Syntax, which you generally don't need.
In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014