• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel sheet linked to another excel sheet - cell format not following

G

gregappelt

New Member
I have a source excel file with 1 worksheet. The file is .xlsx.

When I use the file as a XML Data Import source, all of the data comes over, but the cell formatting is incorrect. Source date of 06/17/1926 results in 9665. If I do a find/replace on the entire worksheet for each number individually, it 're-evaluates' each cell and applies the correct cell format. The same thing happens if I 'F2' the cell and just hit enter.


Is there a macro that I can run that will 're-evaluate' all of the cells and apply the cell format?
 
You should be able to format the destination cells and then setup you data query to preserve cell formatting.
 
Luke, When I go into the Connection Properties of my data source (xml), I don't see any place to retrieve cell format. Also, even though the cells are formated for 'Short Date' or 'Currency', the cell does not apply the format to the data until the cell is 're-evaluated'.


I'm looking for a method to 're-evaluate' all the cells so their formatting is applied.


For a sample - create Source.xlsx with a column of dates. Then create Destination.xlsx where Sheet one gets its data from 'Get External Data' using 'From XML Data Import' - pointing to Source.xlsx.


Greg
 
Luke, I took my own advice and did the Source and Destination files - and it does work as expected.


Back to the drawing board. I believe since I original source came from an xls file, even though it's been resaved as xlsx, is what is causing the issue. I'll try creating a new file and pasting the old xls data as values.


Greg
 
I wonder if this is something that got removed in later versions. In XL2003, you can simply right click on the imported data, and goto to Properties to tell XL how to handle the incoming data (overwrite, add, preserve formatting, column width, etc). Perhaps it's just been moved somewhere?


Anyhow, here's a macro to loop through all the cells in the active sheet with a forced re-entry of data.

Code: 
Sub ReEvaluate()

ActiveSheet.UsedRange.Select

For Each c In Selection

MyValue = c.Value

c.ClearContents

c = MyValue

Next

End Sub
 
You must log in or register to reply here.
Back
Top