Question Opening Excel Spreadsheets

[zzpprk]

Hi

Hope one of you can help me.

I need to open an Excel spreadsheet and extract some data from it. Can I do this from Access VBA code?

Regards

Patrick

 

[twoplustwo]

Is this just a one off thing or will you be using it many times?

Also, do you need a selection of the data from a worksheet, all of the sheet or the whole workbook?

 

[zzpprk]

Hi twoplustwo

Wow!!! So quick. Thanks for assisting.

This is just a one-off thing in the sense that Excel will no longer be required once the data is extracted. This whole process will occur on the click of a button in a form so the user might decide to do this several times but I doubt it. The data I need from the first sheet in the workbook is a group of cells scattered across several columns; guess I can stuff all this data in an array before closing Excel and do my processing on that array or do my processing with Excel still open and close it when the processing is over. Would be nice if Excel remains invisible while open.

Regards

Patrick

 

[twoplustwo]

Yo.

If it's a one off go File -> Get External Data -> Import

Then change the Files of Type to .xls and navigate to your workbook.

The wizard will hold your hand through it to import the data.

Post back if I've got the wrong end of the stick/you encounter problems etc.

 

[zzpprk]

Hi

That won't do because users are really not happy about doing things like that. They want to click a button and the whole thing is done for them without going through wizard screens.

The spreadsheet will always be in the same format so the data extraction process can be automated.

I wonder if I can do this Get External Data thing from VBA code and pass some parameters to tell the wizard a few things about the data.

I immagine the wizard creates a table which I can delete when I am done.

Regards

Patrick

 

[twoplustwo]

"This is just a one-off thing in the sense that Excel will no longer be required once the data is extracted."

Do you mean *per* workbook??

 

[twoplustwo]

    DoCmd.TransferSpreadsheet acImport, 8, "Yourtablename", "filepath of spreadsheet", True, "Spreadsheet range goes here"

Stick that behind a button in the OnClick event or whatev. Probably want to move the excel file or whatever when you've finished to some sort of archive so it can't be imported several times by a click-happy chimp.

 

[zzpprk]

Hi

Sorry if I was unclear.

As far as I can tell, there will only be one workbook which the user will run the processing against. If there are more then the user will just repeat the process. The processing Access needs to perform in only against the data in one workbook.

Hope that clarifies things.

Regards

Patrick

 

[twoplustwo]

I mean the code above will work.

What's the nature of the workbook? Do people fill it in with different info or some such?

 

[zzpprk]

Hi

    DoCmd.TransferSpreadsheet acImport, 8, "Yourtablename", "filepath of spreadsheet", True, "Spreadsheet range goes here"

Stick that behind a button in the OnClick event or whatev. Probably want to move the excel file or whatever when you've finished to some sort of archive so it can't be imported several times by a click-happy chimp.

That has worked!!! Thanks so much.

Now, I get a table with six fields as I've imported six columns. I really need all this data is a single list so, for example, instead of having six records of six fields, I get 36 records of a single field. Is there an easy way of doing this?

Regards

Patrick

 

[twoplustwo]

Patrick, can you explain the process/what it is you are doing for me?

What are these spreadsheet used for?

 

[zzpprk]

Hi twoplustwo

Many thanks for all your help. It is much appreciated.

Basically, I am running checks against a list of names which just happens to be split across six columns in an Excel spreadsheet. A bit daft but there is nothing I can do about the format of the data.

Regards

Patrick

 

[twoplustwo]

Hi Patrick,

Could you go into a bit more detail regarding the nature of the work?

I guess I'm trying to discern whether or not you could transfer the whole process into access using forms/queries rather than spreadsheets.

 

[zzpprk]

Hi twoplustwo

The search is quite tricky because you need to take account of spaces around the name to search. For example, if you search for "WEST" then you want to return "FRED WEST" and "WEST FRED" but not "NORTHWEST". Do you see what I mean?

I have a query that returns all the records that contain "WEST". A peice of VBA code then checks for spaces arount "WEST" depending on wether "WEST" is at the start, the end or in the middle.

The process works well.

The process is made a lot harder because the spreadsheet which the users download from the net contains blank cells and the data to be searched for is split across six columns.

After importing the spreadsheet, I build a list with all the non-empty values in the data range. I then iterate through that list extracting one item and using it to search across the database.

And that works.

Regards

Patrick

 

[twoplustwo]

Why do they dl this from the net?

What is the process actually for?

 

[zzpprk]

Hi

Cannot tell you what it is for. Just that the list is provided by the Goverment and posted on their web site.

Regards

Patrick

 

[twoplustwo]

I don't care what it's for.

Your data sounds non-normalised and I was trying to get to the bottom of your model.

I don't understand why you need to use spreadsheets either for what sounds like data entry.

 

[zzpprk]

Hi

You are right. It would have been much easier if the data was a simple list of text strings in a text file. The reallity is that it is in this weird format in a spreadsheet and that users do not want to even save the dam thing in CSV format. The result is that I am stuck with this spreadsheet and have to deal with it as it is.

Regards

Patrick

 

[twoplustwo]

Hi Pat,

Thanks. Have you ever considered producing a database front end using forms/queries to the back end tables stored on a server?

It sounds as if people are entering data in a spreadhseet and you are trying to insert it into the database... correct? A simple series of forms forcing them to enter the data correctly would make your life a tonne easier, no?

If I am misunderstanding your situation please correct me.

 

[zzpprk]

You are right but I, or the company I work for, have no control on the way the Goverment enters its data.