Wild/Crazy Dreamer - Help?

[Steven Britton via AccessMonster.com]

OK, so this is just a thought - but is it possible for a to open an Excel
file via VBA (I can do that), then while that file is open highlight a
column of part numbers (autofiltered). After they have selected their part
numbers alt-tab back to Access and btnClick to have Access run some queries
with those highlighted cells as look up criteria?

 

[Ken Snell [MVP]]

Possible? Yes.

Easy? Not for people unfamiliar with VBA code and Automation complexities.

Are you sure that you can't just have ACCESS read the full set of possible
values from the worksheet and present them to the user in a form so that the
user can select the desired values for use?

 

[Steven M. Britton]

I took what you said and began to fiddle with it. Yea, that probably would
be just as easy. Is there a way to let the user pick which worksheet they
want to import off of the spreadsheet?

Additionally, how do I create an array from a column out of Excel? I can
open the file, select the records in the column I want, I just don't know
what to do next. Do I take the value in that cells and do a For Next to the
end_row? Sample code?

Thanks again Ken - I always appreciate when you answer my post, you
constantly make examine what I am doing. From you assistance my programs
continue to improve.

 

[Ken Snell [MVP]]

Steven -

Am tied up on a few projects at the moment tonite, so it will be sometime
tomorrow (at earliest) before I can provide an answer ... but I'll be back.
--

Ken Snell
<MS ACCESS MVP>

 

[Ken Snell [MVP]]

Anything that you can do in EXCEL (or pretty much anything) can be done via
automation.

You can open the EXCEL file and loop through the worksheets to get their
names, then present those names in a listbox on a form in ACCESS. The user
can select from the names and allow ACCESS to know which sheet is desired.

There may be a more direct way in EXCEL to store a selected set of cells'
values into an array variable, but here is one way to do it via VBA if a set
of cells have been "highlighted" as a selection:

Dim ln As Long
Dim rn As Range, blnk As Boolean
blnk = False
For Each rn In Excel.Application.Selection
If blnk = False Then
ln = 1
Else
ln = UBound(ss) + 1
End If
ReDim Preserve ss(1 To ln) As Variant
ss(UBound(ss)) = rn.Value
blnk = True
Next rn

 

[Steven M. Britton]

Thanks again for you help Ken. I have been working toward what you have
below. I have the loop to get the worksheet names, I am just have trouble
presenting those names to the user via a listbox. I have been reading and
trying :

http://msdn.microsoft.com/library/en-us/vbaac11/html/acproRowSourceTypeFunctionP
arameters_HV05187987.asp

but haven't been able to get it to work. I also asked about it in the VBA
Programming and received an answer from Dirk, but I wasn't able to follow
what he was telling me...

You can open the EXCEL file and loop through the worksheets to get their
names, then present those names in a listbox on a form in ACCESS. The user
can select from the names and allow ACCESS to know which sheet is desired.

 

[Ken Snell [MVP]]

Using a callback function is more complicated than you really need here.

All you should need to do is build a string text of the worksheet names
(concatenated with ; delimiter). Do this by looping through the
names/worksheets.

Then set the listbox's Row Source Type property to "Value List" and then set
the string to the Row Source property of the listbox.
--

Ken Snell
<MS ACCESS MVP>

 

[Ken Snell [MVP]]

I found the thread that you had going with Dirk. I understand his function
and agree that with his assertion that it would be an easier way to go if
you don't need to open the EXCEL file for any other reason. But, as I
understand what you are seeking to do here, you want the EXCEL file to be
open so that the user can make a selection of cells, right?
--

Ken Snell
<MS ACCESS MVP>

 

[Steven M. Britton]

At first I did, I thought that if they had EXCEL open and made their
selection it would be easier. Then as I begin going through Google and here
reading other's post and working on the code I took your suggestion. "Are
you sure that you can't just have ACCESS read the full set of possible values
from the worksheet and present them to the user in a form so that the user
can select the desired values for use?"

So were I have gotten based on the info you and Dirk have given me is:

User Selects the Excel file containing the information, via windows Open Menu.

Code presents a list of worksheet names so that the user can select which
one to import.

Need to still do-

Actually import that selected tab. (I think I can figure this out no problem)

Then based on what you said I want to present to the user the data of that
spreadsheet in a form, which would come from that table were it was imported
to... Then allow the user to select which column has the part numbers to run
all the inventory queries against. (This I haven't thought through yet...)

A little probably not needed history, I am a newer employee to this company
and the do everything in Excel. This is because the computer system that
houses their data is I believe an AS400 system that sux... So all we have
available are pre-made fixed length text reports - fun. They have a guy here
that has taken most of those reports and recorded macro to get them into
Excel to use... There are "relational" Excel spreadsheet - I am slowing
weaning them away from Excel and doing more and more in Access. However I am
the only one here that I know of that understand Access or VBA at all.....
This makes it tough as I am neither a programmer nor an IT professional. My
current title is BOM Technician in the Product Development and Engineering
Department, and most of my background is in finance. So once again with out
the continued support a help of everyone in these newsgroups especially the
MVP's I wouldn't be able to create the tools that we need to have.

Thanks for the time on the rant.

-Steve...

 

[Ken Snell [MVP]]

At a previous job, I wrote numerous EXCEL macros to convert text files
(delimited, fixed-width, and "database report outputs") into EXCEL
spreadsheets, and to reverse the process. I fully understand what you're
seeing and facing.

For the part about allowing users to "select" the data to use in queries,
how to do that is quite dependent upon what you actually need to display.

If you want the user to see all the data in the columns and to select a
column, use a form (continuous forms view) to display the data in the detail
section; and put in the form header section a series of checkboxes that are
positioned such that one is above each column. Allow the user to check a box
and then click command button. The button's code would identify which
checkbox was checked and then use the corresponding data.

If you just want to display a list of columns and "column headings", then
use a listbox to display column letter (from EXCEL) and column heading.
Allow user to select item and then do as noted above with using the data in
queries.

And so on....

Of course, if you're really ambitious, forgo EXCEL and read in the text file
directly and then manipulate it in ACCESS to get your desired results --
even less overhead to do that.

Good luck!

--

Ken Snell
<MS ACCESS MVP>

 

[Steven M. Britton]

Ken you are quickly becoming my new best friend. I had thought about exactly
both of the options that you described below. I must be learning to think
like a computer now...

I made the decision to go with the former rather that the latter. Reason
being that the EXCEL sheet loaded won't always contain a header row. It may
have formatting junk in the first 6 to 12 rows and the data isn't until later
in the sheet. So I want the user to see what column has the data is check
against and use the check box. But now the problem is that I need to
dynamically create the form each time a user import said EXCEL sheet. I'll
never know how many columns exist and/or which column contains the part
numbers I want to report on... On that I may just skip it and force the
people that want this report to make a new sheet of just part numbers prior
to loading them into Access for comparison.

Of course, if you're really ambitious, forgo EXCEL and read in the text file
directly and then manipulate it in ACCESS to get your desired results --
even less overhead to do that.

Can't do this - There are two files in question here, one is a .txt Report
that contain all of the inventory status for every part in a given plant
(mine is 90k parts) - second is the EXCEL file that has the part numbers in
question. What program is doing is parsing the .txt file into usable data,
then taking the part numbers from the EXCEL “joining the two: and producing a
report that give you inventory status for just the parts that you asked
for...

Thank once again Ken - I am sure you'll hear back from me in another post...

-Steven M. Britton
Access Wannabe

 

[Ken Snell [MVP]]

I have been tied up on some things and haven't had chance to prepare a
reply, but I will....

 

[Steven M. Britton]

No worries I know you have a job as well... But I have changed the way I was
originally going to do this. Mostly based on information located in the
newsgroups by A LOT of the MVP including you. Most everyone said to create
the number of controls you "think" you would need and make them visible or
not. I didn't think this would work for me, because I never knew how many
fields would be in the table.

Then thinking it complete through, I have a form with a subform, the subform
has 10 Unbound text boxes. On load I assign the subforms from a recordset,
then have a left arrow and right arrow to scroll through the fields
reassigning the text boxes control sources each time. Below is the code I
currently have, but's it's not 100% yet. I end up having to make the array
each time I click the button, I haven't figured out how to make the array
onOpen then store it to use for each button.

Talk to you soon,

-Steve

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)

Dim strFields As String
Dim varFields As Variant
Dim i As Integer

DoCmd.Maximize

For i = 0 To 9
Me.FrmSelectExcelColumnSubForm.Form.Controls(i).ControlSource = ""
Next i

Set db = CurrentDb()
Set rs = db.OpenRecordset("ExcelRpt")

For i = 0 To rs.Fields.Count - 1
strFields = strFields & (rs.Fields(i).Name) & ","
Next i

If Len(strFields) > 1 Then
strFields = Left(strFields, Len(strFields) - 1)
End If

varFields = Split(strFields, ",", -1)

If UBound(varFields) >= 9 Then
For i = Me.txtCounter To
Me.FrmSelectExcelColumnSubForm.Form.Controls.Count - 1
Me.FrmSelectExcelColumnSubForm.Form.Controls(i).ControlSource =
varFields(i)
Next i
Me.txtCounter = 0
ElseIf UBound(varFields) < 9 Then
For i = Me.txtCounter To UBound(varFields)
Me.FrmSelectExcelColumnSubForm.Form.Controls(i).ControlSource =
varFields(i)
Next i
Me.txtCounter = 0
End If

Set db = Nothing
Set rs = Nothing

End Sub

Private Sub btnScrollNext_Click()

Dim strFields As String
Dim varFields As Variant
Dim i As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset("ExcelRpt")

For i = 0 To rs.Fields.Count - 1
strFields = strFields & (rs.Fields(i).Name) & ","
Next i

If Len(strFields) > 1 Then
strFields = Left(strFields, Len(strFields) - 1)
End If

varFields = Split(strFields, ",", -1)

For i = 0 To 9
Me.Form.Controls("Check" & i).Value = 0
Next

If Me.txtCounter + 8 >= UBound(varFields) Then
MsgBox "End of File"
Exit Sub
Else: For i = 0 To
Me.FrmSelectExcelColumnSubForm.Form.Controls.Count - 1
Me.FrmSelectExcelColumnSubForm.Form.Controls(i).ControlSource = _
varFields((Me.txtCounter + i))
Next i
Me.txtCounter = Me.txtCounter + 1
End If

End Sub

Private Sub btnScrollPrevious_Click()

Dim strFields As String
Dim varFields As Variant
Dim i As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset("ExcelRpt")

For i = 0 To rs.Fields.Count - 1
strFields = strFields & (rs.Fields(i).Name) & ","
Next i

If Len(strFields) > 1 Then
strFields = Left(strFields, Len(strFields) - 1)
End If

varFields = Split(strFields, ",", -1)

For i = 0 To 9
Me.Form.Controls("Check" & i).Value = 0
Next

If Me.txtCounter = LBound(varFields) Then
MsgBox "Beginning of File"
Exit Sub
Else
For i = 0 To Me.FrmSelectExcelColumnSubForm.Form.Controls.Count - 1
Me.FrmSelectExcelColumnSubForm.Form.Controls(i).ControlSource = _
varFields(((Me.txtCounter - 1) + i))
Next i
Me.txtCounter = Me.txtCounter - 1
End If

End Sub

Private Sub btnSelectColumn_Click()
On Error GoTo Err_btnSelectColumn_Click

Dim Count As Integer
Dim strCheck As String
Dim i As Integer

For i = 0 To 9
If Me.Form("Check" & i) = -1 Then
Count = Count + 1
strCheck = Me.FrmSelectExcelColumnSubForm.Form.Controls("Text" &
i).ControlSource
End If
Next

Select Case Count

Case Is = 0
MsgBox "You select at least column."

Case Is > 1
MsgBox "You cannot select one more then column."

Case Is = 1
DoCmd.SetWarnings False
db.Execute "DELETE * FROM [Inventory Analysis]"
CurrentDb.QueryDefs("qryApdInventoryAnaylsis").SQL = "INSERT INTO "
& _
"[Inventory Analysis] ( [New P/N] ) " & _
"SELECT ExcelRpt." & strCheck & _
" FROM ExcelRpt " & _
"GROUP BY ExcelRpt." & strCheck & _
" HAVING (((ExcelRpt." & strCheck & ") Is Not Null));"
DoCmd.OpenQuery "qryApdInventoryAnaylsis"
DoCmd.SetWarnings True
End Select


Exit_btnSelectColumn_Click:
Exit Sub

Err_btnSelectColumn_Click:
MsgBox Err.Description
Resume Exit_btnSelectColumn_Click

End Sub

 

[Ken Snell [MVP]]

Comments inline....

--

Ken Snell
<MS ACCESS MVP>

No worries I know you have a job as well...

Well, yes and no with regard to a "job"....

But I have changed the way I was
originally going to do this. Mostly based on information located in the
newsgroups by A LOT of the MVP including you. Most everyone said to
create
the number of controls you "think" you would need and make them visible or
not.

This was going to be my recommendation to your last post. Glad that you came
across it already.

I didn't think this would work for me, because I never knew how many
fields would be in the table.

Then thinking it complete through, I have a form with a subform, the
subform
has 10 Unbound text boxes. On load I assign the subforms from a
recordset,
then have a left arrow and right arrow to scroll through the fields
reassigning the text boxes control sources each time.

Hmmm... an interesting approach, though I don't think I would have used it.
I would have approximately 50 or so controls as a "maximum" number of
"columns" and assign each one (to the number of columns there are in the
data) to a field, and make those without a field assignment be invisible. A
lot less programming for your form. But perhaps there is a reason for
needing to "scroll" by changing the fields assigned to each control.

Below is the code I
currently have, but's it's not 100% yet. I end up having to make the
array
each time I click the button, I haven't figured out how to make the array
onOpen then store it to use for each button.

I didn't study your code in great detail, but I assume that "array" means
the array of fields. If you were to use my idea above, your "array" would
just be the form's RecordSource. Otherwise, with your method, I'd use either
a table with one field name per record (with an "ordering" number to show
the order of the fields and to allow your "counter" to work, or I'd use a
module-level variant variable to store the information. I would use the
form's Load event to store the data, not the Open event.

Talk to you soon,

-Steve

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)

Dim strFields As String
Dim varFields As Variant
Dim i As Integer

DoCmd.Maximize

For i = 0 To 9
Me.FrmSelectExcelColumnSubForm.Form.Controls(i).ControlSource = ""
Next i

Set db = CurrentDb()
Set rs = db.OpenRecordset("ExcelRpt")

For i = 0 To rs.Fields.Count - 1
strFields = strFields & (rs.Fields(i).Name) & ","
Next i

If Len(strFields) > 1 Then
strFields = Left(strFields, Len(strFields) - 1)
End If

varFields = Split(strFields, ",", -1)

If UBound(varFields) >= 9 Then
For i = Me.txtCounter To
Me.FrmSelectExcelColumnSubForm.Form.Controls.Count - 1
Me.FrmSelectExcelColumnSubForm.Form.Controls(i).ControlSource =
varFields(i)
Next i
Me.txtCounter = 0
ElseIf UBound(varFields) < 9 Then
For i = Me.txtCounter To UBound(varFields)
Me.FrmSelectExcelColumnSubForm.Form.Controls(i).ControlSource =
varFields(i)
Next i
Me.txtCounter = 0
End If

Set db = Nothing
Set rs = Nothing

End Sub

Private Sub btnScrollNext_Click()

Dim strFields As String
Dim varFields As Variant
Dim i As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset("ExcelRpt")

For i = 0 To rs.Fields.Count - 1
strFields = strFields & (rs.Fields(i).Name) & ","
Next i

If Len(strFields) > 1 Then
strFields = Left(strFields, Len(strFields) - 1)
End If

varFields = Split(strFields, ",", -1)

For i = 0 To 9
Me.Form.Controls("Check" & i).Value = 0
Next

If Me.txtCounter + 8 >= UBound(varFields) Then
MsgBox "End of File"
Exit Sub
Else: For i = 0 To
Me.FrmSelectExcelColumnSubForm.Form.Controls.Count - 1
Me.FrmSelectExcelColumnSubForm.Form.Controls(i).ControlSource = _
varFields((Me.txtCounter + i))
Next i
Me.txtCounter = Me.txtCounter + 1
End If

End Sub

Private Sub btnScrollPrevious_Click()

Dim strFields As String
Dim varFields As Variant
Dim i As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset("ExcelRpt")

For i = 0 To rs.Fields.Count - 1
strFields = strFields & (rs.Fields(i).Name) & ","
Next i

If Len(strFields) > 1 Then
strFields = Left(strFields, Len(strFields) - 1)
End If

varFields = Split(strFields, ",", -1)

For i = 0 To 9
Me.Form.Controls("Check" & i).Value = 0
Next

If Me.txtCounter = LBound(varFields) Then
MsgBox "Beginning of File"
Exit Sub
Else
For i = 0 To Me.FrmSelectExcelColumnSubForm.Form.Controls.Count - 1
Me.FrmSelectExcelColumnSubForm.Form.Controls(i).ControlSource = _
varFields(((Me.txtCounter - 1) + i))
Next i
Me.txtCounter = Me.txtCounter - 1
End If

End Sub

Private Sub btnSelectColumn_Click()
On Error GoTo Err_btnSelectColumn_Click

Dim Count As Integer
Dim strCheck As String
Dim i As Integer

For i = 0 To 9
If Me.Form("Check" & i) = -1 Then
Count = Count + 1
strCheck = Me.FrmSelectExcelColumnSubForm.Form.Controls("Text"
&
i).ControlSource
End If
Next

Select Case Count

Case Is = 0
MsgBox "You select at least column."

Case Is > 1
MsgBox "You cannot select one more then column."

Case Is = 1
DoCmd.SetWarnings False
db.Execute "DELETE * FROM [Inventory Analysis]"
CurrentDb.QueryDefs("qryApdInventoryAnaylsis").SQL = "INSERT INTO "
& _
"[Inventory Analysis] ( [New P/N] ) " & _
"SELECT ExcelRpt." & strCheck & _
" FROM ExcelRpt " & _
"GROUP BY ExcelRpt." & strCheck & _
" HAVING (((ExcelRpt." & strCheck & ") Is Not Null));"
DoCmd.OpenQuery "qryApdInventoryAnaylsis"
DoCmd.SetWarnings True
End Select


Exit_btnSelectColumn_Click:
Exit Sub

Err_btnSelectColumn_Click:
MsgBox Err.Description
Resume Exit_btnSelectColumn_Click

End Sub