Tag Archives: UserForm

How To Convert An Access Database To Excel – PT1

The IT department at most companies installs the MS Office suite on an employee’s computer without Access, but MS Access is a “specialty item”, and has to be requested as a separate install.

Since some folks don’t have the Access program, but they have Excel, in this post I am going to convert my Access database into an Excel format.

Using Access is much quicker and straight forward, but if you have to, you can use this method.

Note: you could have one installation of Access (paid) and then use the Access runtime (free) .

Here is my Access form:

******************************
In order to replicate this, I need a main form / sub form (datasheet) type setup.

I plan to accomplish the main form with just a regular userform, and for the subform (datasheet) I will be using a listbox, and labels as the column headings.

Then, finally, you will find out how to click on the listbox row and display the clicked item, so we can eventually open the compliance form (to do in the next tutorial).
*******************************

Here is what the final result will look like:

First I am going to put all of the data from my database tables into worksheets in the workbook, and add a new worksheet to start from.

This worksheet can be “decked out”, but I’m just going to add a “Start” button, and add a macro to it (as shown in the next few images).

I’ll add an image because it’s more functional for making it visually appealing than the common “button” from the Design toolbar’s toolbox.

Now add the macro to show the UserForm, and attach it to the new rectangle object

Here is the image and the code for the top part of the form

Here is where the data for the top comes from:

And here is the code for the bottom (sub) part of the form. The information pertaining to each station.

Here is where the data for the lower form comes from:

Get The Listbox Value In VBA

To see the detail about a particular compliance inspection, we need to be able to click the sub row and then show a form based on the item that was clicked.

The form is not created yet, but for illustrative purposes, here is how we are going to capture the id to pass to the compliance inspection form.

Stay tuned for part 2 of this.

Any questions so far? Leave them in the comments below, and share this with someone else.

Facebooktwittergoogle_plusredditpinterestlinkedinmail
READ MORE