Issues at opening a Excel file (1 Viewer)

bdra2778

Registered User.
Local time
Today, 08:16
Joined
Feb 14, 2019
Messages
34
Hello everyone, I come to ask for your help by a problem that has me crazy.

I have an application in Ms-Access (used by several users) that obtains information from an excel file through ADODB and is displayed on a form. The excel file is only updated by a person via Ms-Excel. The problem that I have is, if at that time the excel file is open (it is being updated or is simply open), and from my app in Ms-Access we want to get information, two things happen:
1-In my Ms-Access app an error appears (Run time error -2147467259 (80004005)) saying that the excel file can not be opened because it is being used by another user.
OR
2-The information is displayed correctly in the Ms-Access form, but at the same time the Ms-Excel file is opened in read-only mode. Which is very annoying to be closing every time this file in read only mode.

How to avoid these types of problems, there will be some way to avoid this using other way to open a Ms-Excel file?

Thanks for your help.

Attached code using ADOBD.
 

Attachments

  • OpenExcelfile.zip
    39.4 KB · Views: 164
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 08:16
Joined
Oct 29, 2018
Messages
21,358
Hi. Have you tried simply linking the Excel file into Access as a linked table?
 

bdra2778

Registered User.
Local time
Today, 08:16
Joined
Feb 14, 2019
Messages
34
Hi. Have you tried simply linking the Excel file into Access as a linked table?
No, but is a way I will try. Thank for you help.


Update: Same issues:banghead:
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 08:16
Joined
Oct 29, 2018
Messages
21,358
No, but is a way I will try. Thank for you help.

Update: Same issues:banghead:
After you linked the Excel file, you stopped using the ADODB code, correct?
 

bdra2778

Registered User.
Local time
Today, 08:16
Joined
Feb 14, 2019
Messages
34
After you linked the Excel file, you stopped using the ADODB code, correct?

Yes, now use this code

Code:
Private Function F_BuscaObservacion1(strId As String, strCampoBusqueda As String) As Boolean
Dim MyConn As New ADODB.Connection ''Creamos el objeto Connection
Dim rsExcel As New ADODB.Recordset  ''Creamos el objeto Recordset
Dim strSQL As String


 F_BuscaObservacion1 = False

 Set MyConn = CurrentProject.AccessConnection
  
 strSQL = "SELECT * FROM [Observaciones]" ' WHERE [IdCiaFicha]='" & strId & "';"
 
 'Abrimos el recordset
 Set rsExcel = New ADODB.Recordset
 rsExcel.Open strSQL, MyConn, adOpenStatic, adLockReadOnly
 
 F_BuscaObservacion1 = F_BuscaValorRegRst(strId, strCampoBusqueda, rsExcel)
 
 rsExcel.Close
 Set rsExcel = Nothing
 MyConn.Close
 Set MyConn = Nothing
 
End Function

where [Observaciones] is the linked table, Same issues.
 
Last edited:

bdra2778

Registered User.
Local time
Today, 08:16
Joined
Feb 14, 2019
Messages
34
Hellos Guys, having same isues yet, somebody help me please.
 

bdra2778

Registered User.
Local time
Today, 08:16
Joined
Feb 14, 2019
Messages
34
Hi. I downloaded your file and didn't have any problems with it.
There is no problem if only one person is using the Ms-Access database and gets information from the Ms-Excel file. The problem occurs when the person in charge of updating the Ms-Excel file has it open and someone else using the database in Ms-Access wants to obtain information from the Ms-Excel file.

Try to open the Ms-Excel file with other office software such as Open-Office (simulating that it is open on another PC) and use the Ms-Access database, there you will see the error that I tell you.
 

Cronk

Registered User.
Local time
Tomorrow, 02:16
Joined
Jul 4, 2013
Messages
2,770
Two things you could try.
(1) make the spreadsheet shared. Search "Excel setup shared workbook". This enables 2 people to make changes at the same time and it might no longer appear as locked in Access

(2) make a copy of the spreadsheet programmatically and import from that. NB I haven't tried this.
 

bdra2778

Registered User.
Local time
Today, 08:16
Joined
Feb 14, 2019
Messages
34
Two things you could try.
(1) make the spreadsheet shared. Search "Excel setup shared workbook". This enables 2 people to make changes at the same time and it might no longer appear as locked in Access

(2) make a copy of the spreadsheet programmatically and import from that. NB I haven't tried this.

Hello, Sorry for the delay, I've been very busy with a lot of work. I tried point (1) and I still have the same issues. I follow this link https://www.ablebits.com/office-addins-blog/2017/08/02/excel-shared-workbook-share-file-multiple-users/.

I will try the point (2) that I think will be the only feasible solution.
 

bdra2778

Registered User.
Local time
Today, 08:16
Joined
Feb 14, 2019
Messages
34
Hi guys, at the end, the second option worked , create a copy of the file in another temporary directory. Below I leave the code I make in case someone else needs it.

Thanks everyone for your help

Code:
Private Function MakeCopyFile(strDirPath As String, strFileName As String) As String
Dim iFilenum As Long
Dim FullPath As String
Dim NewFullPath As String
Dim TempPath As String
Dim fs As Object

 FullPath = strDirPath & strFileName
 If Not F_DirFileExists(FullPath, "Archivo") Then
   MakeCopyFile = vbNullString
   Exit Function
 End If
 
 iFilenum = FreeFile()
 TempPath = "C:\Temp\"
 NewFullPath = TempPath & iFilenum & strFileName
 If F_DirFileExists(NewFullPath, "Archivo") Then
   Kill (NewFullPath)
 End If
 
 Set fs = CreateObject("Scripting.FileSystemObject")
 fs.copyfile strDirPath & strFileName, NewFullPath
 MakeCopyFile = NewFullPath
 
 Close #iFilenum
 Set fs = Nothing

End Function
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:16
Joined
Sep 21, 2011
Messages
14,044
I *think* what theDBguy was asking, was have you tried a simple link in Access.?
Admittedly I am the same person on my PC, but linked Excel files are read only, so linking an Excel file I had open at the time still allows me to link via the ribbon and open from within Access. ?
If I am wrong, I apologise, but worth a try perhaps?, no code required.

HTH
 

bdra2778

Registered User.
Local time
Today, 08:16
Joined
Feb 14, 2019
Messages
34
I *think* what theDBguy was asking, was have you tried a simple link in Access.?
Admittedly I am the same person on my PC, but linked Excel files are read only, so linking an Excel file I had open at the time still allows me to link via the ribbon and open from within Access. ?
If I am wrong, I apologise, but worth a try perhaps?, no code required.

HTH

Yes I did, but same issues is presented. If you can, please try to open the Ms-Excel file with other office software such as Open-Office (simulating that it is open on another PC) and use the Ms-Access database, there you will see the error that I tell you.
 

bdra2778

Registered User.
Local time
Today, 08:16
Joined
Feb 14, 2019
Messages
34
After other attempts, the only solution was to make a copy of the excel file.
 

Users who are viewing this thread

Top Bottom