Lookup data in Access based on an Excel sheet 1

[kstargold]

I have an access database, it has name, address, zip, phone, and email, and about 20 other check boxes that give the status (returned, going to return, shipped, purchased) of the person, but you don't need to know about what they mean. I also have a list of email addresses in excel.

What I need is a list, in excel of the addresses of the people that I have email addresses for.


I am a pretty good programer in C, Java, perl, html, etc. But I don't know how to program using Office applications, I can guess my way throught VBA using the skill I already have, but I don't know where to go for resoures to start to understand the whole Microsoft approch. If you have a few links about how to use the functions I would be very greatful.

Thanks for the help with my questions
Kanan

 

[kmclane]

Just write a query in access and output it to an Excel spreadsheet. Design your query to give you the data you want, then look at the "outputto" function in Access help. You can setup a macro to do this, then convert it to a module that will let you look at the code to see hoe it is done. Hope this helps, Ken.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[jedel]

Copy the code below into a module:

Function ExcelWizard(strName As String, Optional strItem As String = "Report")
'***************** Code Start *******************
'This code was originally written by Terry Wickenden.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
' Expects the name of the item to be exported = strName
' Also looks for type of object to be exported = strItem
' Defaults to Report - other valid entries are Form, Table, Query
' Note:- These are all case sensitive

  Dim intType As Integer
  Dim strMsg As String
  
  On Error GoTo ErrExcelWizard
  Select Case strItem
    Case "Report"
      intType = acReport
    Case "Query"
      intType = acQuery
    Case "Form"
      intType = acForm
    Case "Table"
      intType = acTable
    Case Else
      MsgBox "Invalid object type", vbCritical, "Entry Error"
      Exit Function
  End Select
  
  DoCmd.SelectObject intType, strName, True
  DoCmd.RunCommand acCmdOutputToExcel
  DoCmd.RunCommand acCmdWindowHide
  Exit Function
ErrExcelWizard:
  Select Case Err
    Case 2544
    ' Invalid object name
      strMsg = "There is no " & strItem & " called " & strName & "."
      MsgBox strMsg, vbCritical, "Entry Error"
      Exit Function
    Case Else
      MsgBox Err & vbCrLf & vbCrLf & Err.Description, vbCritical, "Error Message"
      Exit Function
  End Select
  
End Function

Then simply place a control somewhere on a form (Usually a switcboard or however you navigate through your DB)

and enter the following code in the on_click function:


Call ExcelWizard("Your_tblName", "Table")


You can also call a query, Form or Report.

Normally you would just call a table or Query depend on what you want sent to your Spreadsheet. What will happen is the code will open an excell spreadsheet, transfer the date from the table you have called and name the spreadsheet the same as the table you have called.

This code as show above was written by Terry Wickenden. Has worked realy well for me. I'm sure with your skills you could set it up.

Cheers

Jedel

I'd give my right arm to be ambidextrous!

 

[kstargold]

kmclane, thanks for the overview that go me started on the right track. Thanks for replying.

jedel, thanks for the code, I'm still working with it to tune it to my purposes, but it is much appreciated.


Thank you both again,
you made my life a whole lot easier.

~Kanan