Work on excel file from access vba

[roberto21]

I need to edit a couple of fields of a record in excel sheet, following certain operations in access.The code I am using follows (VBA access).



Private Sub AggiornaLibroSoci(NTes As String)
Dim xlapp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xldata As Excel.Range
Dim ExcelPath As String
Dim rowNo As Long


ExcelPath = CurrentProject.Path & "\"
Set xlapp = CreateObject("Excel.Application")
Set xlBook = xlapp.Workbooks.Open(ExcelPath & "LibroSoci.xlsm")
Set xlSheet = xlBook.Worksheets("LibroSoci")

xlSheet.Select
xlSheet.Activate

' With ActiveSheet
rowNo = xlBook.Worksheets("LibroSoci").Range("C:C").Find(What:=NTes, LookIn:=xlValues).Row
xlBook.Worksheets("LibroSoci").Cells(rowNo, 12) = Year(Date)

If Me.Nuova_TessElett <> "" Then
xlBook.Worksheets("LibroSoci").Cells(rowNo, 37) = Me.Nuova_TessElett

End If
' End With

xlBook.Save
xlBook.Close

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlapp = Nothing
End Sub


This code does not work: I get a compile error, "variable not defined", pointing to LookIn:=xlValues.
Note that:
1) The excel table is linked to the access app, but I cannot edit the linked file directly: Starting from version 2003, Microsoft removed the possibility of editing linked excel files from access due to "legal reasons". So I have to work on the excel file itself.
2) I could import the file, make the changes and re-export the file: but I cannot re-export to the same file, only to a new one, so the process becomes cumbersome
3) The Microsoft Excel 16.0 Object library is already included

I am using Access and Excel in Office 2016.

What am I missing in trying to operate on excel file from Access VBA? Thank you for any suggesrtion.

[Molly Brennholz]

Hello roberto21,
I don't have Access.
I don't have any experience with Access
So I might not be able to help too much…


But some general comments..

_ 1) If the error first occurs at this line,
rowNo = xlBook.Worksheets("LibroSoci").Range("C:C").Find(What:=NTes, LookIn:=xlValues).Row
then I assume that the macro opened the Excel Workbook as you expected. Is that the case?

_ 2) How are you Calling this macro ?


….
_.____________________


I tried this short test:
I made a workbook , "LibroSoci.xls" , and in a worksheet "LibroSoci" , I put some text in like this,
_____ Workbook: LibroSoci.xls ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E

1


2


3xxx


4
Worksheet: LibroSoci

I then saved and closed the file.


I then ran the adaptation of your macro shown below from an Excel File which was opened and which is in the same Folder as the newly created file, "LibroSoci.xls".

The result of running Sub CallSubAggiornaLibroSoci() is that the file "LibroSoci.xls" is opened , the rowNo is correctly determined to be 3, and so the worksheet, ""LibroSoci" is modified to this:
_____ Workbook: LibroSoci.xls ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E

1


2


3xxx
2020


4
Worksheet: LibroSoci

The file "LibroSoci.xls" is then saved and closed


I am not sure if I can help much more as I am not familiar with ACCESS so am not too sure what it is you are trying to do.
But my test suggest that your basic coding idea should work.


_._____

Just one last wild guess… You might want to try changing you signature line to
Private Sub AggiornaLibroSoci(ByVal NTes As String)



Molly

_._______________


Sub CallSubAggiornaLibroSoci()
Call AggiornaLibroSoci("xxx")
End Sub
Private Sub AggiornaLibroSoci(NTes As String)
Dim xlapp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xldata As Excel.Range
Dim ExcelPath As String
Dim rowNo As Long

ExcelPath = ThisWorkbook.Path & "\" ' CurrentProject.Path & "\"
Set xlapp = CreateObject("Excel.Application")
Set xlBook = xlapp.Workbooks.Open(ExcelPath & "LibroSoci.xls")
Set xlSheet = xlBook.Worksheets("LibroSoci")
xlSheet.Select
xlSheet.Activate
' With ActiveSheet
rowNo = xlBook.Worksheets("LibroSoci").Range("C:C").Find(What:=NTes, LookIn:=xlValues).Row
xlBook.Worksheets("LibroSoci").Cells(rowNo, 4) = Year(Date)
' If Me.Nuova_TessElett <> "" Then
' xlBook.Worksheets("LibroSoci").Cells(rowNo, 37) = Me.Nuova_TessElett
' End If
' End With
xlBook.Save
xlBook.Close

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlapp = Nothing
End Sub

Note: Because I have this coding in a normal code module, Me. , would error, so I have ' commented that part out.
( If I had this coding in a worksheets code module, then Me. would refer to the worksheet of that code module: For example , if the coding were in the first worksheets code module, then these two code lines do the same thing:
Me.Range("A1").Value = "Hallo"
Worksheets.Item(1).Range("A1").Value = "Hallo"
)
I do not know what Me. does inside ACCESS

[roberto21]

Thank you very much for your interest. However, I am still in deep trouble, but I still believe the problem lies in the interface Access/Excel.
Usin ByVal in the sub declaration did not change anything.
Your doubt about the excel file being really opened, at this point, is not meaningful, because the code in NOT executed, due to the compile error. However, just to make sure, I tried the following: I declared xlValues as a constant with its enumeration value:

Const xlValues as Integer = -4163

and added the instruction

xlApp.visible = True

With these changes, the compile error disappeared, the code gets executed but the file is not updated. With a breakpoint just before the rowNo = ... line, I was able to look at the opened file and discovered that the file is the right one, but is opened ReadOnly, and that is probably why it does not get the updates. By the way, from what I have read, the default should NOT be Read Only.
Anyhow, at this point the obvious correction seems to be:

Set xlBook = xlapp.Workbooks.Open(ExcelPath & "LibroSoci.xls", ReadOnly=False)

but unfortunately we are back to square 1: now I get Compile error, variable not defined, for ReadOnly. It looks like Access does not know about excel objects, but the Excel 16.0 object library is included in the references.
Thanks again for your attention. Forgive my English, I am writing from Italy
Roberto

[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-programming-vba-macros/
https://www.mrexcel.com/board/forums/microsoft-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...

[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/showthread.php/2419-Test-my-appendages?p=12181&viewfull=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/showthread.php/2418-Work-on-excel-file-from-access-vba?p=12166&viewfull=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(What:=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 )
2744



Here are the files and further explanations:
http://www.excelfox.com/forum/showthread.php/2419-Test-my-appendages?p=12181&viewfull=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(What:=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 )
2745


Here are the two files and further explanations:
http://www.excelfox.com/forum/showthread.php/2419-Test-my-appendages?p=12182&viewfull=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