Another Excel to Access question

blingbling4r

New member
Local time
Today, 02:15
Joined
Dec 31, 2010
Messages
2
I have numerous Excel files that have multiple sheets (all files have the same sheet names). The sheets contain data in specific cells that are all the same however the rows change depending on the Excel file.

In the Excel file I can manually use VLOOKUP to see the required cells from the various sheets like so:

=VLOOKUP("Totals",Ext!1:1048576,12)
=VLOOKUP("Totals",DirLb!1:1048576,3)
=VLOOKUP("Totals",DirLb!1:1048576,3)
=VLOOKUP("Prime Cost",Price Summary!1:1048576,2)

Anyway I can import these values directly to append records in a database?

I want the user to find/open a record in a table. Select the Excel file. Prepopulate the table with the values from Excel.

I'm using the following code on a form (QuickImport) so the user can find the Excel file:

Dim fdlg As FileDialog
Dim sFile As String
Set fdlg = Application.FileDialog(msoFileDialogFilePicker)

With fdlg
.Title = "Select the Excel file to import"
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel", "*.xls; *.xlsx", 1
If .Show Then
sFile = fdlg.SelectedItems(1)
txtFilename.value = sFile
End If
End With

How would I import the information to a select record on a table (Job.Database)??
 
If the Excel sheets are much like a database table, have you considered opening the file through the ADO library and using the recordsets that way?

Or considered using DAO and write the value from one program to another using an Excel object?
 
If the Excel sheets are much like a database table, have you considered opening the file through the ADO library and using the recordsets that way?

Or considered using DAO and write the value from one program to another using an Excel object?

Which one would you recommend? I want this to be used on various platforms (Vista/XP/Win7/etc). In the future I want this Access application to be easily deployable. If they don't have Excel I don't think they can use DAO right?

Sounds like I would have to open through the ADO Library.... how do I do this and find the cells using a VLOOKUP-esque function?
 
Please Keep in mind that my specialty is not ADO, but I do use it.

I want this to be used on various platforms (Vista/XP/Win7/etc). In the future I want this Access application to be easily deployable.
Well, doesn't everyone? ;) It doesn't matter what platform you're on, more than likely you'll have to deal with the frustrations of little changes from Access version to version that Microsoft does not care about publishing (although most have already been detected and published on the web by webmasters and/or Access pros). As far as platforms are concerened, I doubt you'll run into much to tend with between the 3, although Vista I think has the most unexpected problems with it. And, if I remember right, you need to avoid Vista with office SP3 (and maybe other platforms as well), as there were many Access problems like black screens and such when that came out. Sorry, I don't have the link anymore to that discovery, but Bob Larson I believe posted it here somewhere at one time.

If they don't have Excel I don't think they can use DAO right?
Ummm...if they don't have that program, I think you're chancing of getting this done at all is 0, NO?

Sounds like I would have to open through the ADO Library.... how do I do this and find the cells using a VLOOKUP-esque function?
You don't open anything with the library. The library is the DLL file. You simply check the box in your vba references and use the classes (resources) from the script assist (intellisense) to open a data stream. Otherwise called a connection. From my knowledge, you don't want to use ADO if you absolutely have to use built-in functions like vlookup in Excel. ADO recordsets, I believe, act just Access ones, as in there are fields, records, etc, etc... So it is good for querying a table, just like the builder in Access.

Which one would you recommend?
With the above said, it sounds like you need DAO. Create an Excel object and start navigating on through the worksheet after it's open to get what you need I suppose, right? If you're looking for specific cells, I would bet that DAO would be your thing.
 

Users who are viewing this thread

Back
Top Bottom