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
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
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
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
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
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
