Work on excel file from access vba

[Molly Brennholz]

Hello Roberto,

Thanks form the update. That will likely be useful for others coming across this Thread in the future.
As you rightly say, once you know, it seems obvious.

I agree it is bad of Excel not to tell you. In other situation when I open and excel file which is already in use, it does warn me. I don't know why it does not tell you in your case.

I am still no wiser about why xlValues is not recognized by the compiler.
Without ACCESS or any experience with coding inside it I can't help further there.


I am travelling in a few days to do some work at a company, and will be working on a computer with ACCESS.
If I have a spare moment, I will take a look out of interest to see if I can shed any further light on the issue.

---

As I mentioned before, it is fairly easy to get a macro to start when you use open a workbook.

You may know about the following and / or it may be no use to you.

But out of interest I thought I would give it a go: I was just curious myself as to whether it might work.

---

Using the Windows Clipboard

I have uploaded two Files, ( in a spare Thread ( http://www.excelfox.com/forum/showth...ll=1#post12181 ) )

"OpenAndRunMeMacros.xls"
And
"LibroSoci.xls"


Both files should be stored in the same place.

The idea I have is that you then open "OpenAndRunMeMacro.xls" and run the macro Sub MeMacroClitbored()


The macro Sub MeMacroClitbored()
, puts a text from variable NTes into the Windows Clipboard.

It then opens "LibroSoci.xls" in a similar way to how you have opened your "LibroSoci.xlsm" in your original coding. ( http://www.excelfox.com/forum/showth...ll=1#post12166 )

The value from NTes is then retrieved from the clipboard by macro Private Sub Workbook_Open() which is within "LibroSoci.xls".
It uses that variable in a message box and also in the code line
rowNo = xlBook.Worksheets("LibroSoci").Range("C:C").Find(W hat:=NTes, LookIn:=xlValues).Row

That code line is within Private Sub Workbook_Open() of "LibroSoci.xls"

Private Sub Workbook_Open() is in the ThisWorkbook code module of workbook "LibroSoci.xls".

(In the VB Editor, double click on ThisWorkbook to see it :
ThisWorkbookCodeModuleLibroSoci.jpg: https://imgur.com/WYo3jrJ )
ThisWorkbookCodeModuleLibroSoci.jpg



Here are the files and further explanations:
http://www.excelfox.com/forum/showth...ll=1#post12181

---

Note: I tested the above coding in Excel 2010.
Microsoft have made a mess of their clipboards from approximately the year2010 onwards . There are often bugs in software from Microsoft after 2010 which cause problems when using the clipboard. I see that you, Roberto, are using Office 2016. So you may experience problems when using the clipboard
A solution using the clipboard may therefore not work fir you.

Below is a second alternative which uses a text file instead to transfer the variable value in NTes between the two instances of Excel.

---

Using a text File

The basic idea is the same as previously, except I use a text file instead of the clipboard for passing the value in the variable NTes

Similarly as last time, you open "OpenAndRunMeMacro.xls" but this time run the macro Sub MeMacroTextMies ()


The macro Sub MeMacroTextMies ()
, puts a text from variable NTes in a text file, "Timer.txt"
It then opens "LibroSociTextMies.xls" in a similar way to how you have opened your "LibroSoci.xlsm"

The value from NTes is then retrieved from that text file by Private Sub Workbook_Open() in "LibroSociTextMies.xls". It uses that variable in a message box and also in the code line
rowNo = xlBook.Worksheets("LibroSoci").Range("C:C").Find(W hat:=NTes, LookIn:=xlValues).Row

That code line is within Private Sub Workbook_Open() of "LibroSociTextMies.xls"

Private Sub Workbook_Open() is in the ThisWorkbook code module of workbook "LibroSociTextMies.xls".

(In the VB Editor, double click on ThisWorkbook to see it :
ThisWorkbookCodeModuleLibroSociTextMies.JPG : https://imgur.com/ykfDVKJ )
ThisWorkbookCodeModuleLibroSociTextMies.JPG


Here are the two files and further explanations:
http://www.excelfox.com/forum/showth...ll=1#post12182

---

So the point of all that above is´…
If you could somehow get the macros of
Sub MeMacroTextMies()
Sub AggiornaLibroSociTexties()
Sub MeMacroClitbored()
Sub AggiornaLibroSoci()

To work in ACCESS,
and
make the file "LibroSociTextMies.xls" or "LibroSoci.xls" like your "LibroSoci.xlsm" then you would have an alternative way to do what you want to do.



I just added all this to the Thread for future reference. It may not be useful to you.
But I just had the urge to do it anyway


Molly