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

Just reverse the Column property arguments: lst1.ListIndex should be first.

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:accessd-
> bounces at databaseadvisors.com] On Behalf Of William Benson
> (VBACreations.Com)
> Sent: Friday, July 01, 2011 10:04 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com