Import raw data from excel into another excel

Question

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

mattboy · Accepted Answer · 2013-07-23 11:46:41Z

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.

edited Jul 23, 2013 at 11:46

answered Jul 23, 2013 at 7:40

mattboy

2,9085 gold badges29 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Bharath Raja Over a year ago

@matboy - You meant sourceSheet.Range("A1", "C10").Copy targetSheet.Range("A1", "C10").PasteSpecial right?

Arthur Over a year ago

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.

mattboy Over a year ago

@BharathRaja - Duh, yes! I've updated the answer, thanks. Arthur - What goes wrong when you sort?

Arthur Over a year ago

@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

Collectives™ on Stack Overflow

Import raw data from excel into another excel

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related