Excel changing cell formatting on open

[bodmin]

Hi guys,

Bit of a strange one here making me lose some hair, I have a macro in a spreadsheet that compares a list of 2 sets of files to check that all have been processed in both locations this now needs to be done only on files processed on a particular date that is in a column in each spreadsheet.

The problem I am having is when I open one spreadsheet through the macro it is fine and the date format remains in UK type, the other spreadsheet though is changing the date to an american format date and removing the custom cell format and replacing the cell format as a general number format. I have not got any code that is checking or modifying cell formats so bit confused why this would change just by opening the spreadsheet.

Anyone got any ideas?? Obviously I can code around this to reformat that column of data but just wondering why the hell excel would just start changing things??

 

[xlbo]

Please post the code used

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bodmin]

The code used to open the spreadsheet in question is below this is the only code that has been processed that references this particular spreadsheet before the breakpoint is encountered where I can then see the open spreadsheet and have identified that the cell format has changed.

Set xlTewksWB = Workbooks.Open(strXLWorkBook_Location)
Set xlTewksFilesWS = xlTewksWB.Sheets(1)

TewksFilesUsedRows = xlTewksFilesWS.UsedRange.Rows.Count

 

[PHV]

No code in strXLWorkBook_Location ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bodmin]

TewksFileName = Application.GetOpenFilename("All Files (*.*), *.*", 1, "Load Tewkesbury file")
If TewksFileName <> "" Then
'a file was selected so load it into a new workbook
Application.Workbooks.Open TewksFileName
strXLWorkBook_Location = TewksFileName
Else
Exit Sub
End If

The strXLWorkBook_Location is retrieved from a file dialog that is displayed to the user.

 

[PHV]

Again, no code in this retrieved workbook ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bodmin]

Nope the workbook that is retrieved and then opened does not contain any independent code, all code is contained within the comparison spreadsheet.

 

[bodmin]

Hi guys,

Just thought I would let people know I have identified the issue, the formatting has been lost as the spreadsheet is now being delivered in a CSV format that is losing all formatting and dealing with the data as a string.

 

[SkipVought]

INPORT the .csv rather than OPEN.

Format ON TIME.

Data > Refresh on subsequent imports and ALL your formatting can be perserved.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]