Importing Data from Access to Excel

Hi, This is my first qustion in Expert Exchane And I hop I will get the solution from my frinds here.

I want to  get data from my data base which in Access file to my Sheet in the Excel file.
I use the folowing method make the linke.
Firest: In Excel i select data/ Import ExternalData /New  Data Base Query
Then: I creat Select the data source from the ODBC
After That I selct the database and creat My Microsoft Query and save it.

 Evrey thing is Ok
Now I want to  copy my Excel file and my Access file to Another Copmuter  

the problem is that when I Refresh the Excel sheet the a massge said
" Microsft Access ODBC Driver could not find the file 'my fie Path'"
 even when I go to thte ODBC and changr the datasource confgation to the new path of my Darabse the massge diaplayed .

I found that Excel file is linked to the first databas so I have to put my databse in th same path when I chage the copmuer
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dear hadi98,

That's true; using the ODBC wizard to do what you want is generally not a good idea. Use VBA in Access or Excel to accomplish the same thing using the TransferSpreadSheet command. You can also just link the sheet as if it were a table in the Access database, then copy both to another computer relink the Excelsheet and you're done. That last option is by far the easiest way to go about it, no ODBC needed!

Hope this helps,

hadi98Author Commented:
Dear Elbie,
Thanx for yor comment,
 I use the ODBC and Microsfyt Query becuse I want to dispaly  data from more than one Query wich I can't do it using TransferSpreadSheet command
and I don't knowif I can do it using VBA in Excel
pleas How I can display data from more than one query in one SpreadSheet
You can open an Access database in Excel-VBA by instantiating a database object and having it refer to the database containing the data you want displayed. Then, you can fill recordsets with all the data you need (e.g. queries) and use them to fill the spreadsheet.

Below some code for review. It can be used to create pivottables in Excel, but should be easily modifyable to suit your needs. The code is taken from John Walkenback's excellent book on Excel VBA "Excel2000 Powerprogramming with VBA".

Public Sub CreatePivotTableFromDB(ByVal pstrPivotSheet as String, ByVal pstrDBName as String, ByVal pstrTableName as String)

  Dim PTCache As PivotCache
  Dim PT as PivotTable
  Dim strDBName as String
  Dim intLenDBName as Integer  

  'Cut off extention of pstrDBName
  intLenDBName = Len(pstrDBName)
  intLenDBName = intLenDBName -4
  strDBName = Left(pstrDBName, intLenDBName)

  'Delete PivotSheet if it exists
  On Error Resume Next
  Application.DisplayAlerts = False
  On Error GoTo 0

  'Create a Pivot Cache
  Set PTCache = ActiveWorkbook.PivotCaches.Add _
    (SourceType = xlExternal)

  'Connect to database and do query
  DBFile = ThisWorkbook.Path & "\" & pstrDBName
  ConString = "ODBC;DSN=MS Access Database;DBQ=" & DBFile

  QueryString = "SELECT * FROM '" & ThisWorkBook.Path & _
    "\" & strDBName & "' ." & strDBName & " " & _

  With PTCache
    .Connection = ConString
    .CommandText = QueryString
  End With

  'Add new worksheet
  ActiveSheet.Name = pstrPivotSheet
  'Create pivot table
  Set PT = PTCache.CreatePivotTable( _
    TableDestination:=Sheets(pstrPivotSheet).Range("A1"), TableName:=strDBName & " Pivot")

  'Add fields
  With PT
    .PivotFields("DEPARTMENT").Orientation = xlRowField
    .PivotFields("MONTH").Orientation = xlColumnField
    .PivotFields("DIVISION").Orientation = xlPageField
    .PivotFields("BUDGET").Orientation = xlDataField
    .PivotFields("ACTUAL").Orientation = xlDataField
  End With  

End Sub

Hope this helps,


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - Answered by: elbie
Please leave any comments here within the
next seven days.


Comment from expert accepted as answer

E-E Admin
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.