[AccessD] Pulling Data from Excel into Access with "Automation"

There was a typo in the below. Where I said

             So I switched to   ws =
forms!frmCreateTableFromExcel!lst1.column(0, lst1.listindex)

I meant to say

So I switched FROM ws = forms!frmCreateTableFromExcel!lst1.column(0,
lst1.listindex) 

Sorry about that.


-----Original Message-----
From: William Benson (VBACreations.Com) [mailto:vbacreations at gmail.com] 
Sent: Friday, July 01, 2011 9:58 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Pulling Data from Excel into Access with "Automation"

OK, I am leaving the subject line the same, because this was (and mostly is)
an awesome automation exercise, which in fact I will send to Brad after I
perfect it. I have built a tool for examining all fields and the occurrence
rate of distinct values those fields have, within Excel data. Chiefly, from
within all worksheets among open workbooks. There is an option to store this
info for easy reference later, or to clear it out and/or refresh it later. 

One thing I like to use it for is examining data when there are way too many
excel rows to look at cleanly and the number of distinct items exceeds what
Excel will show in the autofilter drop down. I also plan to use this to
build Access tables on the fly from the data in a worksheet, and then save
specific sheets to CSV, perhaps building import specs on the fly using Duane
Hookom's identification of the required tables for maintaining import specs.
But I have an annoying glitch which I have solved through a UDF workaround
but I feel I should not have to do that. Maybe someone can read through this
for the likely issue. Or at least reassure me I have no alternative but the
UDF. Which is fast enough, I suppose - but I would think that the UDF will
be calculated with every row in the result, whereas the parameters I wanted
to use would have been calculated by Access only once then used for every
row).

Basic structure is:

4 controls:
   Lst0 shows open workbooks
   Lst1 shows worksheets which have data in row 1, from the workbook chosen
in Lst0
   LstFields shows all the column headers on worksheet = Lst1
   LstValues shows all distinct values in lstField ... and the number of
occurrences. 
      I store the info in a local table and refresh when desired (workbooks
have to be open in order
      To populate the listboxes.

My Problem:
When creating SQL for the rowsource for lstValues, I tried to reference
listbox columns - but was told by Access that was a syntax error. 

QUESTION!
(I don't know if something like this is supposed to work or not:
       ws = forms!frmCreateTableFromExcel!lst1.column(0, lst1.listindex) -
but it don't. I think maybe because listindex can be -1 and there's no value
and that is a problem.

Here is the complete rowsource SQL
	SELECT WB, WS, Fld, [Values], Items FROM Tbl_Field_Values 
	WHERE		WB = forms!frmCreateTableFromExcel!lst0 
	and 		ws = forms!frmCreateTableFromExcel!lst1  
	and 		fld = forms!frmCreateTableFromExcel!lstfields   


So I switched to   ws = forms!frmCreateTableFromExcel!lst1.column(0,
lst1.listindex). That brought good results except that for some reason I
don't quite understand, the value property of the lstFields listbox was not
adjusting when a statement like lstFields.Selected(i) = was used to select a
different value in the listbox. Sure enough the _AfterUpdate event would
fire, but a test of lstFields.Value would show the same value every time,
regardless. That seems evil and wrong to me!
	==snippet - lstFields.Value didn't change when each row was made the
selection.
	  For i = 0 To lstFields.ListCount - 1
	      lstFields.Selected(i) = True   'no impact on .Value
apparently.
	      debug.print lstFields
	  Next

K, how I solved this, now tell me if I am craze... I used a UDF in the
rowsource, which called the values out of those columns -- which the query
wouldn't let me refer to by themselves: my new RowSource is:

SELECT   WB, WS, Fld, [Values], Items FROM Tbl_Field_Values 
WHERE 	WB = ValuefromList("WB")  
		and ws = ValuefromList("WS") and 
		fld = ValuefromList("Fields") 

'Here's the function
Function ValuefromList(str As String) As String Dim frm As Form Dim strval
As String On Error Resume Next Set frm = Screen.ActiveForm If Not frm Is
Nothing Then
  Dim c As Control
  Set c = frm.Controls("Lst" & str)
  If Not c Is Nothing Then
    If c.ListIndex >= 0 Then
      strval = c.Column(0, c.ListIndex)
    End If
  End If
End If
ValuefromList = strval

End Function