Opening Excel file from within Access

[gtaborda]

Hi

I have the following problem, I hope someone can help me.

I receive excel sheets automatically which have extensions of .XLS however when they open up they "seem to" be TXT files instead. I process these files with an Acess application.

If I just double click on them they open up correctly with excel. I just have to save as ".XLS" instead of the "Text, Tab Delimited" format which seems to always appear in the Save As field.

There is obviously something wrong with the files (seem like they are TXT files but with an .XLS extension).
I cannot change how they are received.

So I am manually opening them, then saving them as XLS. This works and I can then process it with Access.

However with my Access application, if I try to open them (and then save them) with objExcel.Workbooks.Open (the_XLS_File_name), they don't seem to open up like they do when you click on them.

For example, the date fields, which all look as DD/MM/YY when you double click to open the file, are all mixed up as DD/MM/YY and DD/MM/YYYY when open with Visual Basic code.

This obviously causes a problem.

I've also tried to run a macro on excel (Auto) that, when file is open, it is saved as ".XLS" instead of TXT (like I do manually) but this still has the same problem.

Is like the Visual Basic Code doesn't do the same thing as when you double click on the file.

Any ideas how to automatically convert these files and keeping the right format ?

thank you

 

[CautionMP]

gtaborda,
Two thoughts.

Instead of using the [tt]Open()[/tt] method, take a look at the [tt]OpenText()[/tt] method of the Workbooks collection object.
Change the extension from [tt]xls[/tt] to [tt]txt[/tt] then process the files as a tab delimited text file using transfer text.

...
Name "Source.xls" As "Source.txt"
DoCmd.TransferText...

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[lespaul]

are all mixed up as DD/MM/YY and DD/MM/YYYY

This is just a formatting issue. Dates are stored as the number of days since 12/31/1899. So today is 39216.

 

[gtaborda]

it is actually quite weird...there must be something wrong with the file received...

dates are shown as MM/DD/YY and also as MM/DD/YYYY, but one is right justified, and the other is left justified.

But in both cases, if you look at the formula bar, the the full year (2007) is shown, also on those which are MM/DD/YY

On properties of the MM/DD/YY, it is shown Personalized mm/dd/yy

What's even weirder is that if you select all the columns with dates and you change the properties to DATE, format dd-mm-yy ONLY those fields which are currently formated as MM/DD/YY chan ge to MM-DD-YY
In other words, is like the MM/DD/YYYY left justified fields are not taken as dates...

Further more, if you change the column property to NUMBER, only those with MM/DD/YY format change to a number, the MM/DD/YYYY stay as text....

It seems to me like there is something wrong with the file being received, in fact I am sure...but since I can't do anything about the the original file, I need to find a way to change it so it works...

Any ideas???

 

[CautionMP]

gtaborda,

Right Justified means Excel can coherce the text value to a date, formatting will work the value will show as a date (m/d/yyyy) in the formula bar.

Left justified means Excel doesn't think it's a date, so it stores/displays the value the way it is in the file, as a string, which is not subject to date formatting.

If your trying to import the data into Access try option 2 from my first post. Access uses a different set of rules when cohercing data from flat files, so it may be able to do the conversion when Excel can't.

If you change the file extension to [tt]txt[/tt] and link to the file with Access, and the dates are still not correct you will need to look at the text file with a text editor to see what the offending character/format issue that prevents Access (or Excel) from reading the file correctly.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[gtaborda]

thank you all for your replies.

CMP, one of the first things I thought of was to import the excel into access just like you mention, then process it in Access, then save the result as another excel file...

However when I link to the XLS file I get an error, wrong format...cannot understand why.

Anyway, I've tried also to convert the date fields (in Excel) to values (EG. =VALUE(AZ10) ) and the right justified (DD/MM/YY) fields are calculated correctly, however the left justified (DD/MM/YYYY) gives me an error (#VALUE!).

I am going nuts !

CMP, I have also opened the file on NOTEPAD and I am unable to see any problems, unless there are some hidden characters somewhere...

 

[CautionMP]

seem like they are TXT files but with an .XLS extension

If you can open the file in Notepad and see clear text you have a text file with an xls extension (if you open an actual Excel file in Notepad you will see giberish).

Change the file extension and link to it as a text file. You can then process it and export the results to a real Excel file.

CMP



[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)