Work on excel file from access vba

[Molly Brennholz]

Hi,

...Your doubt about the excel file being really opened, at this point, is not meaningful, because the code in NOT executed,....

Ahh yes, you said compile error in post #1,.. sorry my mistake I over saw that.


I see now what you are saying. I agree with you, that a compile error does seem strange, suggesting that Access does not know about excel things. As I do not have ACCESS I cannot check much further..

---

How do you call this macro ?
What I mean is ….. I have used the calling macro
Sub CallSubAggiornaLibroSoci()
to set off Private Sub AggiornaLibroSoci(NTes As String)


What do you do to set off Private Sub AggiornaLibroSoci(NTes As String)

---

We do not have many ACCESS experts looking in at excelfox.
You could also try asking at these places
https://www.excelforum.com/access-pr...ng-vba-macros/
https://www.mrexcel.com/board/forums...oft-access.18/

Please if you ask at other forums, tell everybody of your posts in other forums so that we can all keep up to date on any developments in getting your problem solved.


Molly

[roberto21]

Hi,

---

How do you call this macro ?
What I mean is ….. I have used the calling macro
Sub CallSubAggiornaLibroSoci()
to set off Private Sub AggiornaLibroSoci(NTes As String)


What do you do to set off Private Sub AggiornaLibroSoci(NTes As String)

---

Molly

I am not sure aouyr what you are asking. Anyhow, the code used to call the sub follows

If NDescrizione = "Quote associative di rinnovo" Then
Call AggiornaLibroSoci(Me.Tessera)
End If

in the same module as the sub. This is the code activated when the user clicks on the Save button in access form: as I said, in certain cases I need to update the excel file.


Thanks for the suggestion. I will try to explain the problem in other forums, and I'll keep you informed. Thanks again

[Molly Brennholz]

..., the code used to call the sub follows

If NDescrizione = "Quote associative di rinnovo" Then
Call AggiornaLibroSoci(Me.Tessera)
End If
...

OK, that answers my question. Thanks

---

Another possibility:-
If you can solve the problem of opening an Excel file with ACCESS, then we can arrange that a macro already inside the Excel file starts when the Excel file is opened.
( I am not sure how you would transfer the information .... (Me.Tessera)
One possibility could be to put it in the Clipboard from ACCESS, then retrieve it from the Clipboard from Excel.
I have done this putting in and retrieving from Excel. Possibly the coding is the same in ACCESS. I am not sure. )

I do not know why you are experiencing problems in opening an Excel File from ACCESS.
Here for example, it is suggested that it can be done: http://www.vbaexpress.com/kb/getarticle.php?kb_id=527 .

Hopefully an ACCESS expert might have a better idea and can help you further

[roberto21]

I found the solution to the problem of excel opening in read only mode. It was obvious, as are many solutions AFTER you have found them. It was also written in my first post: the table is linked to the access app, therefore the workbook is to be considered open. When I try to open it again with workbooks.open, excel can only open it in read-only mode. One may complain "why didn't excel say so, that the file was already open"? This I don't know, and would like to know.
Anyhow, I replaced

DoCmd.TransferSpreadsheet acLink,...
with
DoCmd.TransferSpreadsheet acImport, ...

and now the .xlsm file opens correctly in read/write. Thank you everybody for your attention and interest. Now, if I or you could find the reason why xlValues is not recognized by the compiler, even though
it is correctly listed in the object browser...