from access to excel

[miiia83]

hi!

is it possible to show information in excel from an access database?
If I for example choose "B" from a scrolldown list in excel... then the info that belongs to the B person would appear...
if that makes any sence??.. kinda hard to explain

Mia

 

[llkhoutx]

Absolutely.

Better get a good reference manual. Maybe some kind heart has some code for you.

I generate and format an XLS with VBA in Access. It's not for the feint of heart and not as simple of coding for mere Access.

 

[Mike Smart]

hi!

is it possible to show information in excel from an access database?
If I for example choose "B" from a scrolldown list in excel... then the info that belongs to the B person would appear...
if that makes any sence??.. kinda hard to explain

Mia

Hi Mia

It is quite possible to do this elegantly with VBA code. Generate an SQL query, submit it to the Access database, walk the recordset and populate an Excel Range object with the result (via the Cells collection).

Another approach might be to create the SQL query from within Excel interactively (Data-->Import External Data-->New Database Query) and then construct a parameter-driven query to return the data you need. You'd still need to use VBA then to automate the display of the result and to lift the value from the source cell.

When I run my Excel and Access VBA courses to solve specific business problems I nearly always find that when companies try to do this sort of thing they are mis-using Excel as a database when what they should be using it for is an Analysis tool to analyse data properly stored in a database.

 

[miiia83]

I didn't really understand that...

I want the FUNCTION_CODE to be shown as an listbox.. and then when you choose "1H" out of the listbox.. then DESCRIPTION should show the info that belongs to "1H".


I was just wondering if anyone knows what the listbox code looks like???

Sub Get_Category()
    resrow = 3
    rescol = 2
    Sheets("sheet1").Range("b2:j1000").ClearContents
    

    OpenConnection ("RFC")
    'DESCRIPTIONS = tabellnamn
    strQuery = "SELECT * FROM DESCRIPTIONS"
    Set Rs = ConnRFC.Execute(strQuery)
    Do Until Rs.EOF
        Sheets("sheet1").Cells(resrow, rescol).Value = Rs("FUNCTION_CODE")
        Sheets("sheet1").Cells(resrow, rescol + 1).Value = Rs("DESCRIPTION")
        
        resrow = resrow + 1
        Rs.MoveNext
Loop

Kill Connection ("RFC")

Mia

 

[miiia83]

nobody knows?

 

[llkhoutx]

It's not a list box, but two columns (Function code & Description) filled with your recordset data.

 

[miiia83]

yes I know that it is just two columns right now... but i was wondering how to change it into a listbox!

 

[llkhoutx]

Use the SQL string (SELECT * FROM DESCRIPTIONS) as the row source for a listbox.

Other listbox properties will have to be appropriately, particularily, columns and column widths.

 

[miiia83]

the problem is that I'm a newbe at this and what you just wrote said me absolutely nothing!
thanks for trying though!

 

[llkhoutx]

I've attached a zipped A2K mdb which displays a listbox.

Not particularily the following properties of that control:

Row Source (note that it is sorted on column 1; first column is column 0)
Bound Column (column 1; different from column 1 above' Access anomoly)
Column Count
Column Widths
Width
Multiselect (None, simple, or extended)

The index column is usually hidden, i.e. Column Widths would be 0;1.75 to hide the first column.

The Multiselect property determines how many rows can be selected at once.

 

[miiia83]

Maybe I've confused you...
I wanted the listbox to be in excel.. with info from an access table..
I know how to make a listbox in access

 

[llkhoutx]

You should post your question on an Excel forum.

You have to push or pull you data into Excel, them populate your Excel list box. I don't know if you can have a dynamic link. I've never used an Excel list. box or used dynamic data in Excel. I've always pushed data into Excel from Access.

Why aren't you doing everything in Access?

 

[miiia83]

When people use the program I don't want them to be able to mess with the tables and codes..
I guess it's not possible to have a link from a webpage that only opens a form from the file and not the whole database... or is it??

 

[llkhoutx]

Access Security isn't the strongest, but keeps 99.9% out.