Display and Excel File

[colmtourque]

Display an Excel File

I'm rather lost here and was recently thrust back into the world of access and vba. Any help would be appreciated. I'm trying to create a database that I can use to import any number of different excel files of many exceedingly different formats and merge them into one format. I think I've come up with a good solid method but I have one problem. I'd like to be able to display the excel file on a form so that it can be previewed and I have had no luck making this happen. I've been searching the web for a coupld of hours and as I said it's been a while and my vba is very rusty but I can't seem to get either then unbound or the bound source control objects to work. Can some please show me how you use a button to display an excel file as listed with full path in a text box on an access form so that it can be previewed complete with row numbers and column headings.

 

[DCrake]

Use can use list boxes to show files in folders and common dialog control to navigate to folders, but I don't think you can actually display a spreadsheet in a Access form. You can however double click on a file and shell to Excel and open it in Excel.

You say you have come up with a method to import data from various layouts. How is Access going to know where the data is to be found is normality does not exist.

David

 

[colmtourque]

I'm letting the user map the fields, it's really the only way. Kind of like the way you do an import of a csv into outlook, except not quite as pretty. once i have it in a nice format I'll post it, right now it's barely alpha.
How can I display excel data in a subform based on a file chosen name in a main form text box. I'm trying this.

My main form is FileImporter my sub form is FileImporterSubForm and the subform name is SubFormFI:

Me.SubFormFI.Form.RecordSource = "SELECT * FROM [Excel 8.0;HDR=Yes;database=C:\Documents and Settings\blah blah blah\Copy of E-Checks 080309.xls;].[Sheet1$A3:E99]";
Me.SubFormFI.Form.Requery

But all that does is hang excel at calculating.

The whole sub that this is in opens the excel file pulls column headings into text file and then I would like it to also display the file somehow so that if the user needs to make any adjustments they can. Not adjustments to the file but to the import.

Oh, is there a way to change my post title so I don't look quite so illiterate?

 

[DCrake]

If your workbook has column headings and data why don't you let the user navigate to the workbook and upon selection you dynamically link the workbook and set the subform rowsource as being the named workbook sheet1. When the form closes it unlinks the workbook.

David

 

[colmtourque]

How would I do that? Sorry, I have not delt in VBA or access for some time. I've been during pure sysadmin and some sql but my vba is VERY rusty (and it was never great to being with).

 

[colmtourque]

On the other note I just realized a big part of the problem. While there are headers they are sometimes all over the place (it's a long story). They could be in row 3 or in row 7.
Let me start from scratch here and explain the idea here. We get dozens of files in from many different companies. Some are pretty straight forward flat csv's. Some are text, some are excel. The format of each one can be a flat file with all the column headings in row 1 to a very not flat file with the column heading in any of a couple different rows. Sometimes the data does not even line up to the correct column. What I am doing is allowing people to view the column data and decide if EXCEL column A is indeed say Name or if for this file it's actually address or phone number. Then they choose from the final format we want it to be and state what the data is. The can the go column by column and choose what EXCEL column they want the data to go to. I need them to be able to preview the file so it's simpler.
Hence the need to simply display the excel spreadsheet flat in Access. To date I can pull single cell data to text boxes but that has been the extent of my success.

 

[DCrake]

I think it is now time to get out your big stick from under the desk and point it at your customers. And, with a raw smile on your face tell them that this is the format I want you to send me the data in. Here is a template for you to use, if you chose not to do so, then this can dely the processing of your data.

If you are providing a service and as a result they benefit from it then it is only fair that they assist you in the best way possible. It's a case of GIGO - Garbage In Garbage Out, or as I like to say SISO.

Test this out with a few of your more agreeable customers first to test their reaction. Also any new customers need to be informed of the layout prior to their initial submission.

Let me know how you get on.

David

 

[colmtourque]

Sadly that is not a realistic option for numerous reasons. I have to build a fool proof solution that accounts for far to many fools for the big stick to fix. It sounds like there is a way I would love to hear it or be pointed in the right direction.

 

[DCrake]

You seem to be in a similar situation as the three little pigs. You know what the end result is... to build a house... however if you use the wrong materials it will never be stable enough to live in.

You only viable solution in this case is to eyeball all incoming spreadsheets and organise the data in such a way that it can be imported into Access using a common procedure. Other than that you would have to have individual import routines for each variation of the data. This can be more cumbersome in the long run. Good Luck.

David