Importing Multiple Files to Single Workbook date issue

**kevinbowe2000** · 10-25-2012, 06:06 AM

Hi all,

I am having an issue with a macro I am writing and can't get to the bottom of it.

The aim is to import multiple files in a folder into a single workbook. These files are tab delimited files.

The issue is, that the first column of all files is a date and time.
When I import and data using the below macro, the date and time gets all messed up.
The raw date and time is in UK format but when I import all the files to the single workbook, some of the dates get changed to US format and some don't.
I want all the dates to be in UK format as that of the raw data.

Any ideas what the issue might be?

Any help would be most appreciated.

Thanks in advance.

_________________________________

Please Login or Register  to view this content.

**arlu1201** · 10-25-2012, 06:25 AM

Did you try pastespecial (xlpastevaluesandnumberformats) ?

**kevinbowe2000** · 10-25-2012, 06:40 AM

To be honest, I am very new to VBA.
Not sure where I would put that in.
I am not pasting anything.
Just using the worksheet copy function to bring one worksheet to the other workbook
Is it possible to do a worksheet copy with a pastespecial?

**kevinbowe2000** · 10-25-2012, 06:45 AM

I have also tried to use Workbooks.OpenText instead of Workbooks.Open, which I thought would keep all cells as text strings, but keep getting a
Compile error:
Expected Function or variable

**arlu1201** · 10-25-2012, 06:50 AM

Instead of doing a worksheet.copy, you can try copying the contents of the worksheet. That way, you can use a pastespecial command.

**kevinbowe2000** · 10-25-2012, 07:13 AM

Got to the bottom of it.
Just had to put in Local:=True in the .open syntax.

Thanks for the help. Most appreciated.

**arlu1201** · 10-25-2012, 07:41 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

I will do it for you this time.