Linking Data from Access into Excel

[shyam131]

Does any know of a method to link an Access Query to an Excel spreadsheet. One caveat I only need specific Feilds. For example if an Access Qeury called "2002 data" has Fields 1-10, How would I link Fields 2 and 5 into an Excel Spreadsheet? I know about DDE, but this seems to be obsolete?!?

Thanks

 

[nberryman]

Use File, Get External Data in Access to link to the spreadsheet.

Then just use it as an Access table, bear in mind that queries tend to be case sensitive when linking Excel sheets



Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[shyam131]

Sorry, I think you misunderstood me. I need to do the opposite. I have the data in Access and need to Link it into Excel. That is changes to my Access Database will automatically be updated in my Excel Spreadsheet

 

[SkipVought]

Shyam131,

No, nberryman answered you question CORRECTLY.

This method will create a query of your Access table in Excel. After you create it as nberryman outlined, all you have to do is Date/Refresh Data.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[FastEddieB]

I want to link Access queries to an Excel spreadsheet, instead of using DDE.......

In my queries, I am using a Moduld called "IsInDay, so that any data time stamped between the hours of midnight and 3 AM, is considerde to be the previous days data.

Here is the code:

Option Compare Database
Public Function IsInDay(ByVal pdtDate As Date) As Boolean

Dim dtStart As Date
Dim dtEnd As Date

dtStart = DateValue(pdtDate) + TimeValue("3:00AM&quot
dtEnd = DateValue(DateAdd("d", 1, pdtDate)) + TimeValue("3:00AM&quot

IsInDay = ((pdtDate >= dtStart) And (pdtDate <= dtEnd))

End Function

Public Function ShiftDate(ByVal pdtDate As Date, _
ByVal pdtTime As Date) As Date

Dim dtStart As Date

If pdtTime < TimeValue(&quot;3:00AM&quot Then
ShiftDate = DateAdd(&quot;d&quot;, -1, pdtDate)
Else
ShiftDate = pdtDate
End If

End Function


When I go to use Microsoft Query in Excel to set up the Link to the Access database, I get the following error

Undefined function &quot;ShiftDate&quot; in Expression

Does anyone know how I can get Excel to recognize this function. Any assistance would be greatly appreciated.

 

[SkipVought]

FastEddie,

Please post in a NEW THREAD.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884