Cross Excel documents data validation

Resolved · Low Priority · Version 2016

Thu 12 Jul 2018, 11:30 Delegate Luke said...

Luke has attended:
Excel VBA Intermediate course

Cross Excel documents data validation

Hi guys,

I want to create a drop down box in a document which takes its data from a table in a different excel document (the database). This cell should be allowed to have any data added to it, not just the options in the the database.

Any advice?

Thanks
Luke

Fri 13 Jul 2018, 11:21 Trainer Jens said...

RE: Cross Excel documents data validation

Hi Luke,

Thank you for the forum question.

It is not an easy task.

You will need to code it. You will need creating a connection to the the database workbook.

Excel can only do it if you use ADO (ActiveX Data Object). The ActiveX Data Object library must be activated in the Visual basic editor and the connection string must be declared in the computer's memory.

If you are not using ADO the database workbook must be open all the time to use the drop downs.

I have found something which can help you in the right direction.

https://technet.microsoft.com/en-us/library/ee692882.aspx

If it was me, I would create an array in the destination workbook, where I would store the data received from the connection.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Fri 13 Jul 2018, 16:47 Delegate Luke said...

RE: Cross Excel documents data validation

Thanks Jens

That has really helped, we have managed to get it to create the list without opening the original document or creating a new tab. The one issue we are still having is that we can only set it to look at a set number of cells ("B3:B552") and not the entire column for the dynamic table.

Thanks again

Luke

Fri 13 Jul 2018, 16:51 Trainer Jens said...

RE: Cross Excel documents data validation

Hi Luke,

If you paste the code in the forum, I can may be spot where you have the issue.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Fri 13 Jul 2018, 16:59 Delegate Luke said...

RE: Cross Excel documents data validation

Hi Jens
Here you go:

Sub sbADOExample()

Dim sSQLQry As String
Dim ReturnArray

Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset

Dim DBPath As String, sconnect As String

'The customer database path
DBPath = "T:\msd\Services\MF\Measurement Services\Customer Database\Customer Database.xlsx"

'Connect to the excel file
sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"

Conn.Open sconnect

'Create the connection string - specify the data range you wish to read
sSQLSting = "SELECT * From [Customer Database$B3:B552]" ' change this to read from the table rather than the exact data range

'Open the query
mrs.Open sSQLSting, Conn

'Create data validation using the recordset
If Not mrs.EOF Then
ary = Application.Transpose(Application.Transpose(mrs.GetRows))
With ActiveWorkbook.Worksheets(1).Range("A1").Validation
.Delete
.Add Type:=xlValidateList, Formula1:=Join(ary, ",")
.ShowError = False
End With
End If

'Close Recordset
mrs.Close

'Close Connection
Conn.Close

End Sub

Fri 13 Jul 2018, 19:08 Trainer Jens said...

RE: Cross Excel documents data validation

Hi Luke,

I can only get SQL to work with a fixed range but I found out a walk around this.

If you change the line to as many rows you will need in the future (see below)

sSQLSting = "SELECT * From [Customer Database$B3:B20000]"

And then after open the recordset transfer the recordset to an array.

Dim Myarray as Variant

myarray=mrs.GetRows

and then count the strings in the array.

For Each ArrItem In MyArray
If ArrItem <> "" Then
lcount = lcount + 1
End If
Next ArrItem

Then you can resize the array to only include the strings.

ReDim Preserve MyArray(0, 0 To lcount - 1)

And then reference MyArray in the Transpose function

ary = Application.Transpose(Application.Transpose(MyArray))
With ActiveWorkbook.Worksheets(1).Range("A1").Validation
.Delete
.Add Type:=xlValidateList, Formula1:=Join(ary, ",")
.ShowError = False
End With

I hope this make sense

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector