0

I'm using the following Code to import a raw data extract (mysql; output format *.xlsx) from an excel sheet (source) into a sheet (target).

Importing Excel spreadsheet data into another Excel spreadsheet containing VBA

The target sheet is a excel-template contains some macros e.g. to allow users to easily sort data.

All is working very well, except that some data are malformed after import. Its really strange but due to any reason target is not an exact copy of the source sheet. Issues I've for example with date format and decimals.

I've tried several things to tweak the code but failed. I believe that the way how the import in target sheet is handled by code is the reason why it doesnt work.

Now my question is if there exits any other way to import data from excel to excel. Also I'm looking for a solution how I could solve the problem that excel formats all values automatically as text?

Any help would be highly appreciated.

Kind regards

Community
  • 1
  • 1
Arthur
  • 9
  • 3

1 Answers1

0

Try replacing this line

targetSheet.Range("A1", "C10").Value = sourceSheet.Range("A1", "C10").Value

With this

sourceSheet.Range("A1", "C10").Copy
targetSheet.Range("A1", "C10").PasteSpecial

The former only copies the values. The latter copies both values AND its formatting, so things like date formats and number of decimals should be included as well.

mattboy
  • 2,870
  • 5
  • 26
  • 40
  • 1
    @matboy - You meant sourceSheet.Range("A1", "C10").Copy targetSheet.Range("A1", "C10").PasteSpecial right? – Bharath Raja Jul 23 '13 at 09:22
  • Many thanks for the advise mattboy. The import works (source into target) and formats are ok as far as I can see. The only problem I face, is that sorting does not work. – Arthur Jul 23 '13 at 09:30
  • @BharathRaja - Duh, yes! I've updated the answer, thanks. Arthur - What goes wrong when you sort? – mattboy Jul 23 '13 at 11:48
  • @mattboy - sorting is really strange. Date (format DD.MM) shows the following: 28.2 28.2 28.2 28.2 30.11 30.11 30.11 30.11 30.12 30.5 30.6 30.6 Same issue I've with some other rows. it shows data as follows: 99,580 93,000 9,980 9,960 9,927 9,900 9,850 9,800 9,790 9,600 9,559 9,550 9,544 9,456 9,405 9,399 9,290 9,229 9,205 9,190 9,130 9,090 9,020 9,010 87,120 87,000 87,000 86,900 – Arthur Jul 23 '13 at 15:02