Hi there,

I have Access 2007 and want to bring in an Excel spreadsheet as a linked table, so that I can update it from Access.

Looking at the MS Help files, it appears that their idea of a link in 2007 is one-way. I cannot make changes to the table. It is not dynamically linked.

I assume that this is because there is some other way of doing it.

Can someone explain?

Thanks,
BP

I assume that this is because there is some other way of doing it.

Can someone explain?

The problem is due to a legal decision which made it so that Microsoft could not have two way links to an Excel spreadsheet (they had a ruling go against them right around the Excel/Access 2002 time period, if I remember correctly) and that meant that in all versions moving forward from that point, you can link to Excel to GET data but you cannot link to it to send it back.

You will have to export to get it back to Excel.

Thank you for that Bob.

In that case I revert to the problem I gave up on before.

I wrote the code in Access to run a routine in Excel.

I've tried what I can but I still get that error:
Cannot run the macro 'mymacro'. The macro may not be available in this workbook or all macros may be disabled.

The routine works fine in Excel
The Access code opens Excel and the correct spreadsheet
I have every switch for allowing macros to be enabled set in Excel
The Excel routine is Public

If I could get it to work, I wouldn't need the link - that was my Plan B. Now it's back to Plan A.

Any advice?

Thanks,
BP

Why not export the information directly and not use a macro on the Excel sheet?

That means getting to grips with coding for Excel, from Access. I'm not good at that, but I'll give it a go.

It will help me if you could tell me the code for Access 2007 that will:

Open Excel
Open a specific Spreadsheet
Go to a specific worksheet
Find a specific value in a cell
Write to a cell in the same row
Save the spreadsheet
Close it all tidily

I know that's a lot to ask, but I can probably take that and run with it.

Thanks

To show I'm not being totally lazy, here's my attempt at the shell parts:

Sub AddValueToColumn(lOrderID As Long, sType As String)

On Error GoTo Err_cmdRunExcelCode_Click
Dim XL As Object
Dim XLOpen As Boolean

XLOpen = False
If OpenExcelOK(XL, "myspreadsheet.xlsm") Then
XLOpen = True

... what goes here...

End If

XL.Quit
Exit_cmdRunExcelCode_Click:
Exit Sub

Err_cmdRunExcelCode_Click:
MsgBox Err.Description
If XLOpen Then XL.Quit
Resume Exit_cmdRunExcelCode_Click

End Sub

That means getting to grips with coding for Excel, from Access. I'm not good at that, but I'll give it a go.

It will help me if you could tell me the code for Access 2007 that will:

Open Excel
Open a specific Spreadsheet
Go to a specific worksheet
Find a specific value in a cell
Write to a cell in the same row
Save the spreadsheet
Close it all tidily

I know that's a lot to ask, but I can probably take that and run with it.

Thanks


No problem:


Dim appXL As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet

Set appXL = New Excel.Application
Set xlWB = appXL.Workbooks.Open("C:\FilePathAndFileName.xls")
Set xlWS = xlWB.Worksheets("SpecificSheetNameHereInQuotes")

xlWS.Range("G5").Select ' to select a specific cell

xlWS.Range("H5").Value = Me.ControlNameFromAccess

'or if you have a recordset open

xlWS.Range("H5").Value = rst!MyFieldName.Value

xlWB.Save

appXL.Quit

Set appXL = Nothing


That is the real basics and we can work with you to do other things.

Also you might check out these code pieces from my website which might give you some ideas:

Query/Table to Excel (http://www.btabdevelopment.com/main/CodeSnippets/SendTableQuerytoExcel/tabid/144/Default.aspx)

Form Recordset to Excel (http://www.btabdevelopment.com/main/CodeSnippets/SendFormsRecordsettoExcel/tabid/120/Default.aspx)

And then we'll show you how to modify your code so that you can get rid of any Excel Reference and it will then work with any version of Excel (or almost all), without throwing reference errors.

Wow, thanks for that Bob.

I will have a go at that.... tomorrow I think. You may just be getting going, but it's time for me to stop for the evening over here!

Have a great day,
BP

Okay, I've made excellent progress thanks to your help.

I had to work out how to add the reference bit, as that was required, and the following line seems to do what I need.

Application.References.AddFromFile ("C:\Program Files\Microsoft Office\Office12\Excel.exe")

Now my question is this: Can I test for this reference?

Reason is, it seems to hold the reference for the whole Access session, not just run time, so instead of trapping errors, I'd rather just do a test and if it's there, skip the line.

Thanks,
Phil

Hey, I don't mind continuing on my own.

I solved this by looping through all the references and seeing if there was one called Excel. If not, then activate it.

Simple enough.

Now, why when I use:
appXL.Quit
Set appXL = NothingDo I still see Excel in the Task Manager?

PB

The following opening and closing of Exdel leaves the taskmanger clear for me. Office 2003 ans XP Pro


Dim objXLApp As Object
Dim objXLBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("C:\Letters\ExtractEmailDetails.xls")
Set oSheet = objXLBook.Worksheets(1)

'objXLApp.Visible = True

objXLApp.Run "Macro1"

objXLBook.Close True

objXLApp.Application.Quit


But if you want to guarantee a real clean kill:D

taskkill /F /IM Excel.exe
But that will kill all Excels that are open. Taskkill is not in XP Home but it can be copied to it.

Now, why when I use:
appXL.Quit
Set appXL = NothingDo I still see Excel in the Task Manager?

PB


What is the FULL code you are now using. It is likely you referenced an Excel item NOT explicitly from an object and that has created a separate instance of Excel which will hang around until you close Access or kill it in the task manager.

Okay, deep breath, it goes like this:

This is the reference bit, in the main routine :

bAlreadyActive = False
For Each objTemp In Application.References
If UCase(objTemp.Name) = "EXCEL" Then
bAlreadyActive = True
End If
Next
If Not bAlreadyActive Then Application.References.AddFromFile ("C:\Program Files\Microsoft Office\Office12\Excel.exe")
This is where I use the Excel bits, in a seperate module:


Sub AddAmount(lAmount As Long, iType As Integer)

Dim appXL As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim xlRange As Excel.Range

Dim AddDate As String
Dim FindDate As Range

Set appXL = New Excel.Application
Set xlWB = appXL.Workbooks.Open("C:\Users\...\sales.xlsm")
Set xlWS = xlWB.Worksheets("Sales (2009)")

Set xlRange = xlWS.Range("A:A")

Do lots of stuff

xlWB.Save
appXL.Quit
Set appXL = Nothing

End Sub

is where I use the Excel bits, in a seperate module:


Do lots of stuff

It's that DO LOTS OF STUFF part I need to see. It is IN THERE where you are causing the extra Excel instance to appear.

Second, get rid of the reference code. You don't need it. Once you have gotten this all done, you can remove your reference and change the code to LATE BINDING. Basically, you would remove your reference in Tools > References and the change this code:



Dim appXL As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim xlRange As Excel.Range

Dim AddDate As String
Dim FindDate As Range

Set appXL = New Excel.Application

to this:

Dim appXL As Object
Dim xlWB As Object
Dim xlWS As Object
Dim xlRange As Object

Dim AddDate As String
Dim FindDate As Object
Set appXL = CreateObject("Excel.Application")


Also, one thing that can be causing your problem now is that you have

Dim FindDate As Range

When I believe you want

Dim FindDate As Excel.Range

unless you are thinking of it in some other sort of thing.