Export to Excel w/ lookup?

[flygal5]

Various companies with data. Need to send to Excel. But in Excel, how can I
do this so that if I pull the company name, data would autopopulate (this
data coming from Access) Thanks.

 

[Steve]

Please speak in a little more intelligible English so we can understand and
can help you.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[flygal5]

Sorry about that.

I have a d/base of accounts: different companies that have certain data.
We also have an excel spreadsheet template where we would like to be able to
use the Access d/base info into this excel template. Is there a way where we
can export the access data to this template so that the company name would be
a dropdown box. For example, when we select ABC Company, the data
corresponding to ABC would populate the template with corresponding fields in
the access database?

 

[flygal5]

Sorry about that.

I have a d/base of accounts: different companies that have certain data.
We also have an excel spreadsheet template where we would like to be able to
use the Access d/base info into this excel template. Is there a way where we
can export the access data to this template so that the company name would be
a dropdown box. For example, when we select ABC Company, the data
corresponding to ABC would populate the template with corresponding fields in
the access database?

 

[John W. Vinson]

Various companies with data. Need to send to Excel. But in Excel, how can I
do this so that if I pull the company name, data would autopopulate (this
data coming from Access) Thanks.

Create a Query joining your data table to the lookup table, and export from
that Query.

John W. Vinson [MVP]

 

[John Nurick]

Export from Access in the normal sense, no.

Build an Excel template containing dropdowns and VBA code to make it all
work, yes. This may help: it's an Excel macro that takes the value in
specified cell (or the values in a range), looks it/them up in an Access
table like DLookup(), and places the result(s) in another cell (or
range).


Sub GetData34(Keys As Range, Values As Range, _
DatabaseName As String, Table As String, _
KeyField As String, ValueField As String, _
KeyFieldType As String)

'Works through all the cells in Keys, looking up each
'value in Table.KeyField,
'grabbing the value of ValueField in the same record,
'and placing it in the corresponding cell in Values.

'Warning: not yet thoroughly tested. As it stands
'will fail on text keys that contain apostrophes.

'Some of this (but not the buggy bits) is based on
'Allen Browne's ELookup() function.

Dim dbEngine As Object 'DAO.dbEngine
Dim db As Object 'DAO.Database
Dim rs As Object 'DAO.Recordset
Dim strSql As String
Dim strKeyValue As String
Dim j As Long

'Open database
Set dbEngine = CreateObject("DAO.DBEngine.36")
Set db = dbEngine.OpenDatabase(DatabaseName)

For j = 1 To Keys.Cells.Count
'Build the SQL string.
strSql = "SELECT TOP 1 [" & ValueField & "] FROM [" & _
Table & "] WHERE [" & KeyField & "] = "
Select Case LCase(KeyFieldType)
Case "string", "text", "memo"
strKeyValue = "'" & Keys.Cells(j).Value & "'"
Case "date", "time", "date/time"
strKeyValue = "#" & Format(Keys.Cells(j).Value, _
"mm/dd/yyyy") & "#"
Case Else
strKeyValue = CStr(Keys.Cells(j).Value)
End Select
strSql = strSql & strKeyValue & ";"
Set rs = db.OpenRecordset(strSql, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
Values.Cells(j).Formula = ""
Else
Values.Cells(j).Formula = rs.Fields(0).Value
End If
rs.Close
Next j

Exit_GetData34:
Set rs = Nothing
db.Close
Set db = Nothing
Set dbEngine = Nothing
Exit Sub

Err_GetData34:
MsgBox "Error in GetData34 at row " & j & ". " & vbCrLf _
& "Error " & Err.Number & ": " & Err.Description, _
vbOKOnly + vbExclamation, "Database lookup"
Resume Exit_GetData34
End Sub