Using ADO with Excel

[Stefano]

Goodmorning everyone,
my first post on this awesome forum, and yet a question for you experts
First of all, I'm not a programmer nor a coder, of any type... but I'm playing with VBA from some months.

So, the problem: in our company we need a practical way to manage components database, and thus to compile data sheets with them.
All the job has been manually done for years, one excel sheet per component (~ 600), and data sheets compiled manually row by row, component by component.

Now I decided to optimise this tedious and unsafe method:

• I put all the components in ONE file organised like a database (from now let's call it DATA_SOURCE)
• I created a model of data sheet (from now let's call it COMPILED) to be populated with data from DATA_SOURCE

Obviously every data taken from DATA_SOURCE have to be refreshable, in order to keep always everything up to date.

And here the problems.... as not a programmer, I probably miss the basis of everything about it!

What I have so far:

• DATA_SOURCE, an xlsm file with 20 sheets (one for each component type), and every sheet has a table and a named range (to sort from)
• COMPILED, another xlsm file, with a custom form to choose components from

Now, in COMPILED I've set up an user form with some combo box to select components
User form connect to DATA_SOURCE via this string:

    cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & matWb & ";" & _
        "Extended Properties=" & Chr(34) & "Excel 12.0;IMEX=1;HDR=YES;ReadOnly=1;" & Chr(34)

and with recordsets and some other code enables other combo boxes etc....
It works quite fine... but is this the right way??

Then, when I've selected my component I will press the "OK" button to write data on my sheet, but I cannot do this with previos connection, and I have to set another one with this code:

Private Sub BTN_ok_Click()
    closeRS
    closeRS2
    sConn = "ODBC;DSN=Excel Files;DBQ=" & matWb & ";"
    Set oQt = ActiveSheet.QueryTables.Add(sConn, ActiveCell, strSQL)
    With oQt
        .FieldNames = False
        .HasAutoFormat = False
        .AdjustColumnWidth = False
        .RefreshOnFileOpen = falso
        .BackgroundQuery = False
    End With
    oQt.Refresh
End Sub

Another time... is this the right way to do this??
Selected data populate the file.. every time I insert a component excel creates a new connection (and I think it is right, because each component point to a different sheet/row in DATA_SOURCE)
But when I refresh connections.... a lot of data disapper!


All the code is taken from various sites and adapted to my needs, but I'm not sure I've done everything well... (in fact I'm sure something is wrong!)

Can you please help me?




I do not know if I explained it well... in any case I will attach a pair of pictures, but if somebody will take care of it I will attach the entire code and example files too.



DATA_SHEET.jpg
COMPILER.jpg

Thanks!

[Stefano]

so, has anybody any idea how to solve this problem?

[Aussiebear]

Actually, its hard to test any code just by looking at the pictures. You no doubt mean well in presenting pictures, but I'm sure most people would rather a sample workbook to play around with. Are youable to provide one?

[Stefano]

I'm preparing them!
Thanks!

[Stefano]

Ok, here attached the 2 files to test, DATA_SOURCE and COMPILER (put them in same folder).
COMPILER.xlsm
DATA_SOURCE.xlsm


If you need additional information ask me, and if someone will find a solution he will become my new hero!


Tahnks!

[Stefano]

No ideas?

[SamT]

But when I refresh connections.... a lot of data disappear!

What is supposed to happen?

To help us get a better idea of the feel of the Project, speaking as an office manager to a new employee, tell us why, when and how to use the Form.

Please don't speak programmer to programmer, just boss to worker.

lookin over the code in Compiled I see that you are addressing ActiveCell

Private Sub BTN_RECSET_Click()
    closeRS
    closeRS2
    rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic    
    Do While Not rs.EOF
        ActiveCell.Select
        Selection = rs.Fields(0)
        ActiveCell.Offset(0, 1).Select
        Selection = rs.Fields(1)
        rs.MoveNext
    Loop
    LabVerClear
End Sub

I think you must use a different way if you want to append data to the table

Private Sub BTN_RECSET_Click()
Dim NextRecord As Range

    closeRS
    closeRS2
    rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic

If Not rs Is Nothing Then 
Set NextRecord = Sheets("Foglio1").Cells(Rows.Count. "C").End(xlUp).Offset(1, 0)
   
    Do While Not rs.EOF
        NextRecord = rs.Fields(0)
        NextRecordOffset(0, 1). = rs.Fields(1)
        Set NextRecord = NextRecord.Offset(1, 0)
        rs.MoveNext
    Loop
Else
MsgBox "The Recordset " & strSQL & " was not opened."
End If
    LabVerClear
End Sub

This code has a VBA spelling error

Private Sub BTN_ODBC_Click()
    closeRS
    closeRS2
    sConn = "ODBC;DSN=Excel Files;DBQ=" & matWb & ";"
    Set oQt = ActiveSheet.QueryTables.Add(sConn, ActiveCell, strSQL)
    With oQt
        .FieldNames = False
        .HasAutoFormat = False
        .AdjustColumnWidth = False
        .RefreshOnFileOpen = falso '<------------------ "False"
        .BackgroundQuery = False
    End With
    oQt.Refresh
End Sub

You can avoid such errors by placing "Option Explicit" at the top of code pages. However once you do that, you will have to make many other corrections to the code. Use Debug >> Compile to find all the required corrections without running the Forms.

There are some things I can't help with because I am using Excel 2002 and they are not available to me.

[Stefano]

Hi SamT, and thankyou for your answer.
Probably everything is so clear in my head that I've not been able to explain well

Step by step:

1. now in the office we have an only file for components, and we can use it like a database (yes, we know Excel is not a database, but we already know we can use it in this way for small projects)
2. we need to build, or maybe "compile", technical data sheets for our products
3. so we open our COMPILER file, and we discover it has a FORM inside
4. now with this form we should be able to fill the data sheet, component by component
5. so, we start searching for the first component, selecting field by field in the USERFORM, and when we find what we need, we click on OK button (pay attention that different components will have a specific position and row number on our sheet, that's why I used "Activecell": I want to be able to tell the form exactly where to put data)
6. component data are automatically inserted in the selected row of our data sheet
7. now I can go on inserting other components. I notice that the form is useful because I don't have to re-open it for every component, and it is also non-modal, letting me select the cell where I want data to be entered.

Ok, this is the process to compile data sheet; every data sheet will be compiled this way.

Now, suppose something changes in our database, maybe component description or price... I will do all the changes, and then resave my file.

And here the donkey falls (as we say in Italy): I open one of my compiled data sheet, and I want it with updated values from my database file.
So I go to DATA menu to refresh all connection.
And ideally all data will be refreshed.... but in fact the content of some rows disappear.
And I don't know why...

Here I attach a picture of a compiled data sheet, so you can see what the final result will be

DS_example.jpg

[SamT]

OK

So you have a database workbook organized with one component Type per sheet.

You have a product line and you want to create or Compile, a data sheet showing all the components used in each product.

You want the user to Pick a Row on the Product Data Sheet, Choose a component, and Insert the data into the next row,

For example, to insert a new Ponticello, select a Cell in Row 20, Use the form to choose a component, then the code will insert a row below row 20 and add the Component data to the new row 21.

If the User Chooses another Component without first Picking a new Row, then assume to insert the new Component Data into the next Row down

You want to refresh all the component's data on all the product sheets when you change any data in the database Workbook.

I am goning to change the thraed Title to "Using ADO with Excel."

[Stefano]

OK
You want the user to Pick a Row on the Product Data Sheet, Choose a component, and Insert the data into the next row,
For example, to insert a new Ponticello, select a Cell in Row 20, Use the form to choose a component, then the code will insert a row below row 20 and add the Component data to the new row 21.
If the User Chooses another Component without first Picking a new Row, then assume to insert the new Component Data into the next Row down

Sam,
it is all right, except quoted part.

The number of rows is fixed, so if I want to insert a Ponticello on Row 20, I will pick cell B20 (on A20 there will be numbering) and use the form to insert data, that will be inserted on B20, C20, D20 etc.
If I choose another component without changing cell selection I will simply overwrite existing data.

This is the main part, then there will be other little things to fix, but I think we can think about them when this part is done.

[SamT]

This is the main part, then there will be other little things to fix, but I think we can think about them when this part is done.

That is the easy part, it is something we do every day.

The Product data sheet shown in the picture in post #8 is very customized and is different from the sample Product product Data sheet you have in Foglio1 in the Compiler.xlsm workbook you uploaded.

The best way to do things really depends on the final form of what you want. The best way to create a sheet like Foglio1 is not the best way to create a sheet like the picture in Post #8. There are at least 99 ways to accomplish anything with VBA, but only half of those are the best ways.

Tell us what the final resulting Product Data Sheet(s) should be. I am thinking that you may be wanting each Product to have a custom formed Data Sheet. This will take a bit more engineering and forethought. I my guess is correct, please show us two or three samples of different Product Data sheets so that we can understand what we may have to think about.





Please note that at this time, I am trying to gather information for all those who may be watching this thread. I see at least 6 ADO speakers in the list below as well as our resident Function Expert. I do not speak ADO and would not use SQL to accomplish what you need. I would only use VBA for Excel. You should understand that there are many ways to do anything with VBA. If ADO will work, it can be very elegant code.

Are you convinced that you want to use ADO?

[Stefano]

Ok, first of all thank you again for your patience.
Then, tomorrow morning I will be in office and I will post some data sheets, so you can understand well what we need to achieve.
In fact the file I posted was only the beginning, I thought to customize it later... but maybe it is better to start the right way.

I'm sure there are a lot of ways to do everything just in VBA, but as I said I'm quite a newbie, and I don't know all of them, the only thing I can do is search, think and try
Instead I'm not sure about ADO and SQL (and I don't speak ADO too), I only found them as a quick way to obtain my goal (thinking about DATA_SOURCE as a database, where entries may increase or decrease).
If we find a way to do everything in VBA it is ok anyway, the important thing is that the final product works fine!

[Stefano]

Here I am, with 4 data sheets of 4 different products, and 2 images that will explain exhaustively (I hope) every aspect of the final product.
It is not so simple... but if you take a pair of minutes the process will be clear, I think.

Now the question... what is, from the 99 possibilities, the right way to accomplish this?


DS_expl.jpgDS_steps.jpg

DS_example_1.jpgDS_example_2.jpgDS_example_3.jpgDS_example_4.jpg

[SamT]

Please! Just upload the sheets. Pictures are impossible to properly interpret.

IS it that you have a limited line of products that all Product Data Sheets have the identical layout for POS 1 to POS 16?

I can see that this will require 7 lists of Range Addresses and each bit of Data must be individually assigned to a Range Address. This is not difficult, merely cumbersome.





You should understand that it is difficult for VBA to deal with Merged Cells on a worksheet. However, if you will Format the Cells using Horizontal Alignment = Center Across Selection, then VBA can handle the issue with great ease and the Worksheet appears identical to one that has Merged Cells.

[Stefano]

Please! Just upload the sheets. Pictures are impossible to properly interpret.

If I upload only sheets I think is quite difficult to understand what part of layout is fixed and what not.

Anyway, we have about 500 products, and thus 500 data sheets.
Components may vary, they can be present or not, but every data sheet have identical layour from pos 1 to 16.
Then in fact there are 8 list of range Addresses (1-4 5-7 8-12 13 14 15 16 17-32).

Merged cells are not a problem... I've been a graphic designe for 10 years, in general everything must be in his place and I don't want to see useless cell, but I think I can manage it!

[SamT]

Fixed Fields can be merged or formatted any way the Graphic Designer wishes.

Dynamic data should not be in a Merged Cell. Center Across Selection can make several adjacent Cells in a Row appear to be merged.

The creative use of white Fill, white Borders, and light grey Borders can complete the illusion of Merged Cells.

After the Graphic Designer in you has finished the final formatting, please Fill all the Dynamic Cells that VBA must deal with with a color. I think that we should write the Addresses list. After we choose one of the 99 ways to list them.

[Stefano]

Hi Sam,
graphic designer inside me is suffering...

Anyway, here attached you will find an empty model of DATA SHEET with 3 sheets (plus settings) and a lot of colors
I think everything should be quite clear now, and we can start thinking about the right way to manage this!

To write down the Addresses list I need the DATA_SOURCE complete file, that is in my pc in the office. I will post it as soon as I arrive there tomorrow morning.

DATA SHEET MODEL.xlsm

[SamT]

This will make your little Graphic heart happy

Graphically Designed.xls

Briefly, the green cells are the ones we need VBA to refer to.

I will be using that to ponder various referencing schema.

The only problem is... It is in English and the Data Source is not. I will be looking at your pictures above to correspond the two.

[Stefano]

Perfext, I'm very happy

In fact we can also use the DATA_SOURCE I've already uploaded to do the work, it miss only MICA, IONIZERS and some others component types (because the final and complete file is not yet finished).
Once the present components are referenced I can do the tedious part to duplicate for the other, I think.

So here the referncing schema:

POS	DATA SHEET	DATA_SOURCE
1-4	MICA	missing
5-7	WIRE	_FILO_
8-12	CABLE	_CAVI_
13	DIODE	_DIODI_
14	THERMOSTAT	_TERMOSTATI_
15	IONIZER	missing
16	THERMOFUSE	_TERMOFUSIBILI_
17-32	ALL THE REST	_CAPOCORDA_, _PONTICELLI_ , _SUPPORTI_ , _VARIE_ and some others...

[SamT]

I am sorry, but the Sheet formatting in Graphically Designed.xls has broken the formulas in the other sheets.

I have more to say, but Home Work is calling me.