CODEWITHSUNDEEP
excelms-accessimportexcel-2007
user955289
user955289

Reputation: 171

Import to Excel from Access table based on parameters

I am attempting to create a template that will utilize data from an Access table, and allow the user to select the parameters in the Excel file. The amount of records in this table is slowing down the Excel file, and I would like to lessen the amount of data that is imported based on the user selection.

The Access table will have three columns:
Part Number|Line|Catalog Code

I would like to import all three columns, but allow the user to select the records imported based on the catalog code.

The users do not have read or write access to the sql tables, so I run an Access query at the end of each day to create an Access table that they can access.

I have 2007 versions. Is this type of import possible?

Thanks in advance!

Upvotes: 1

Views: 16967

Answers (2)

Fionnuala
Fionnuala

Reputation: 91376

From Access to Excel example.

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim s As String
Dim i As Integer, j As Integer

''Access database

strFile = "z:\docs\dbfrom.mdb"

''This is the Jet 4 connection string, you can get more
''here : http://www.connectionstrings.com/excel

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";"

''Late binding, so no reference is needed

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

'Rough idea
intID = Sheet1.[A1]

strSQL = "SELECT * " _
       & "FROM Test " _
       & "WHERE ID = " & intID

rs.Open strSQL, cn, 3, 3


''Pick a suitable empty worksheet for the results

Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rs

''Tidy up
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

Upvotes: 2

Stephen Turner
Stephen Turner

Reputation: 7314

You can do this without VBA, just by using MS Query (works in Office 2010, I can't check against '07):

  • in Excel go to the data ribbon and click the From other sources icon
  • click From Microsoft Query
  • select MS Access Database*
  • browse for and select your database.
  • in the wizard select the columns you need to import
  • on the next page select the column you want to filter on
  • select the type of filter you need, i.e. Equals
  • instead of selecting a value in the next box enter [Parameter please Bob]
  • enter you sort on the next page
  • select Return data to Microsoft Excel and finish

MS Query will ask you for the parameter, then Excel will ask you how and where you want the data. and your filtered data will appear.

Now each time you refresh the data you will be asked the parameter again, so you can easily change it.

p.s. If you don't want a 'programming' answer, ask on superuser.com

Upvotes: 4

Related Questions