Solved: Combine Worksheets

[shiv jalli]

Hi,

I tried running the excel macro contained in VBAX Kbase
"Combine All Data From All Worksheets in All Workbooks in a Specified Directory"

It works great for text and number but transposes date from source to target. 23/10/06 in the source results in 10/23/2006 in the target.

Please help.

[lucas]

Could you post the workbooks here so we can take a look at what might be happening?
Click post reply and then scroll down and look for manage attachments.
you may have to zip them up...

[stanl]

Hi,
It works great for text and number but transposes date from source to target. 23/10/06 in the source results in 10/23/2006 in the target.

Please help.

My best guess is that the date format on the source needs to be reset in the target. .02 Stan

[lucas]

From Matt's description in the mentioned kb entry:

Suppose you have several workbooks in a directory, each with one or more sheets and you want
to put all the worksheets from all the workbooks into one (or more, if necessary) worksheet.
This macro does all the work for you. Each worksheet must have the same structure.
Link to the entry:
http://vbaexpress.com/kb/getarticle.php?kb_id=773 (http://vbaexpress.com/kb/getarticle.php?kb_id=773)

Joseph also has an entry that addresses this issue but works around the "must have same structure" issue......it also skips blank sheets....worth a look:
http://vbaexpress.com/kb/getarticle.php?kb_id=829 (http://vbaexpress.com/kb/getarticle.php?kb_id=829)

[shiv jalli]

As requested I attach a zip file. The zip file I downloaded from Kbase contained "Combine sheets from files" with the code macro and 3 sample data sheets in the subdirectory.

I have not changed the code macro in "Combine sheets from files" but have just added an additional (date) column to the sample 1 spreadsheet and deleted sample 2 and sample 3 to simplify matters.

I look forward to further help.
Many Thanks, Shiv.

[lucas]

Shiv,
I'm not getting the same problem that you mentioned in your first post....are you trying to combine all of the worksheets from the subdirectory into just one sheet or do you wish to import the individual sheets into your workbook....3 sheets in your attachment?

[Norie]

Shiv

Are you sure the actual date has been 'transposed'?

Perhaps it's just a formatting issue?

[shiv jalli]

Hi Steve.

The operation I perform is to open "Combine sheets from files" and run the macro "CombineSheetsFromAllFilesInADirectory".

A new sheet "Sheet1" is created that contains details from Sheet1 thru to Sheet3 in sample 1. The new "Sheet1" has all the same detailed see attached but header7 in this sheet shows 10/23/2006 as opposed to 23/10/2006 in sample 1. I am running this macro in the UK with locale set to English United Kingdom.

Hope this help to clarify details.
Regards, Shiv.

[lucas]

Ok Shiv,
I think I understand..I added this to the end of the code to format column G.

Columns("G:G").Select
Selection.NumberFormat = "d/m/yyyy"
Range("F16").Select

Let us know if this helps. See attached

[shiv jalli]

Unfortunately, the dd/mm are still being swapped. Glad to try any further suggestions. All contributions welcome.

Shiv.

[lucas]

That's odd...it works for me...maybe you could try a different custom format.

[shiv jalli]

Steve, when it works for you does it show:
a) 10/23/2006 or
b) 23/10/2006

in both source and target

Regards, Shiv.

[austenr]

My two cents worth. I remeber a post similar to this one a while back. It probably is the version of EXCEL (european) that sets the date by default to dd/mm/yy. Try changing the dropdown to American in the Format Cells Date dialogue box. Of course if that is what you want and you have highlighed the cells you want formatted that way.

I think the default is the country you install it in but not sure.

[lucas]

Steve, when it works for you does it show:
a) 10/23/2006 or
b) 23/10/2006

in both source and target

Regards, Shiv.
I get b

[shiv jalli]

Steve, Austen: Many Thanks for your help.

Steve I used your code change and updated the macro to use value2 and not value and everything works fine. How strange!!.

Keep up the good work guys, you are stars.
Shiv.

PS. I attach the updated zip.