Excel importing fun!

[Smee]

Hey guys.

I've searched around a bit on this one, but I'm not finding what I need.

Staff here used to fill out timesheet information on a sheet of paper, hand it to an admin staff member and she then typed it into a Foxpro database.

Recently, with progress being made on my Access system that is going to be replacing the foxpro, Staff have been prepared for the eventual automation of the timesheet process by filling in an excel spreadsheet. Because the Access hasn't been finished yet, these are then printed, and then typed in to the foxpro.

I am now at the stage where I need to automate the timesheet process, and import the spreadsheet data into the timesheet table of the database.

However I've hit a few problems and the import wizard I think is now useless to me.

I need to :-

- ignore the first few rows, as these are just things that make the excel sheet look nice for the staff to be using.

- import all the data after the first rows (starts from row 7)

- the number of rows needed after row 7 may vary (from just 1 entry to maybe 20 or more) so I need some way for the code to check if the first cell of each row is null or not. If null, then move on to the next row : if 2 nulls in a row then presume end of timesheet and finish.

- Take an entry from 1 cell from the first row and make it the source for a column along side the other records. i.e. Cell E5 has 200407 and I want a column called year prd that has that value for every record in it.

Any help for any 1 or all of these problem greatly apprieciated.

If you need to see an example of the spreadsheet or the existing table then let me know.

Thanks

 

[neileg]

The great thing about Excel is that you can do all sorts of dirty fixes that Access wouldn't tollerate. What I would do here is insert a second worksheet in your workbook and pull the data from the nicely formatted version that your users complete. In this second sheet you can do what you like with the data to make it compatible with your database.

When Access imports from a spreadsheet, it ignores empty rows so you needn't worry about that.

No doubt you can come up with a fancier way to do this, but I'm just a hacker and quick and dirty suits me every time!

 

[Parker]

I would take it one step further than that and create a new work book with just one worksheet.

Let your users continue to use the origonal and link the two. Sorta like a middle man. That way you can make the new work book exactly what you need for Access with no formating or formula. It also means that you have no fear of someone changing something that you didn't need changing etc. If there are any calculations that need to be done leave it to Access to sort it.

A better way, of course, would be to recreate the form in access and change everyone over. Why use two tools to do the same job?

 

[Smee]

Thanks for your ideas guys.

I've spent a while trying to link to a new workbook without the fancy formatting. I've got the cells updating automatically etc, but I don't see how I can automate the task of getting them into Access or making these extra sheets.

There is a spreadsheet for each person (a timesheet for each person) and a new worksheet for each 2 week period.

That means, if I have a linked equivilent spreadsheet without the formatting I will have to make one for each of the 26 periods a year for each of the 22 staff - thats a lot of work.

I need to either automate the making of these access friendly sheets or find some way of using the existing ones within access or vba.

I don't think I've explained this too well, but maybe I have.

Thanks

 

[The_Doc_Man]

If you wanted to do this with VBA, Excel is pretty nice about how it presents things.

You can either do by-row or by-column scans. If there is something you can look at to determine whether a particular row is a time row or some other kind of row, you can do simple VBA.

The idea in overview is,

Open an Application.Excel object.

Assuming multiple worksheets with multiple names, you could do a search with FindFile to find all the .XLS files. Perhaps with a partial name template.

For each file you could open the file, which would become one of the entries in, I believe, the workbooks collection. Each sheet in the workbook becomes a member of that workbook's worksheet collection.

You can traverse open workbooks with workbooks and open worksheets with workbooks.worksheets(m) - or you can select a particular sheet to make it the ActiveSheet (analogous to Access shortcut Me, meaning the current form.)

An active sheet has two collections. One is called Rows, the other Columns. The contents of a cell are just ActiveSheet.Rows.Cells(m) (and there might be further qualifiers).

Now, all you need to do is figure out how to examine each workbook and worksheet to identify the one you want, then look for the rows you want, then start direct-extracting data from each affected cell. Probably into a recordset of some sort for which you have set up Append rights.

If you do the vba approach, don't forget to always close what you open. Otherwise you will eventually run out of some memory-based resource. It's a crap-shoot as to which one goes first.

 

[Smee]

Great post thanks -some very useful tips in there.

Thanks

 

[neileg]

Thanks for your ideas guys.

I've spent a while trying to link to a new workbook without the fancy formatting. I've got the cells updating automatically etc, but I don't see how I can automate the task of getting them into Access or making these extra sheets.

There is a spreadsheet for each person (a timesheet for each person) and a new worksheet for each 2 week period.

That means, if I have a linked equivilent spreadsheet without the formatting I will have to make one for each of the 26 periods a year for each of the 22 staff - thats a lot of work.

I need to either automate the making of these access friendly sheets or find some way of using the existing ones within access or vba.

I don't think I've explained this too well, but maybe I have.

Thanks

Ah, you want us to solve your solution! You decided to use Excel to capture the data, now you want to solve the problems caused by using Excel. Take a step back and look at the problem afresh.

 

[Smee]

Ah, you want us to solve your solution! You decided to use Excel to capture the data, now you want to solve the problems caused by using Excel. Take a step back and look at the problem afresh.

I'm sorry, what?

I haven't decided to capture the data in excel at all. The data is already there. I'm importing it into Access.

I wasn't asking you to solve the solution either. I had followed someone elses advice as far as I could and was asking them to elaborate.

I need to either automate the making of these access friendly sheets or find some way of using the existing ones within access or vba.

The_Doc_Man offered the advice I needed to answer the above and I've solved most of the problems now using vba.

Ah, you want us to solve your solution! Take a step back and look at the problem afresh

I can't help get the feeling you were taking a little stab at me there, as well as being condescending.

This may all just be a misunderstanding, but I have to respond to the message as I understand it (you were probably doing the same).

If it is then apologies all around and thanks again to The_Doc_Man for the help that got me through the problem.

 

[neileg]

If you're happy, I'm happy!

 

[The_Doc_Man]

thanks again to The_Doc_Man for the help that got me through the problem.

Glad to help.

Sometimes this is the only place where I feel have helped anyone.

But then, that is a common side effect of working around the U.S. Government. "Hi, I'm from the government and I'm here to help you..."

 

[Mark Wild]

To put a slightly differrent spin on this, I do a similar update from a large number of people accross the business, but rather than use Excel, I have an Outlook form that users complete and then sent to a specific mail box.

Admin then from time to time run a process in Access that imports all the data frm the forms.

If your users have Outlook, this solution might be wirth looking into...

 

[mr moe]

Does anyone have an example for the above, I'm asked to do the same, basically import formatted excel forms into access tables. Please Please Please help me if you can. thanks.