Solved

open recordsed of excel file read only issue

Posted on 2014-12-09
14
225 Views
Last Modified: 2014-12-11
Hi Experts,
I am trying to read records from an excel file using the code below, however I am getting an error message when user has that file open, although I only need read only access.
   conn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ=C:\Application;"
   rs.Open "SELECT * FROM [Application.csv]", conn, adOpenStatic, adLockReadOnly, adCmdText

Open in new window

how can I avoid this issue?
0
Comment
Question by:bfuchs
  • 7
  • 5
  • 2
14 Comments
 
LVL 84
ID: 40489965
I don't believe Excel will allow you access if someone else has the file open. All you can do is report that to the user and let them try later.

You may be able to use Automation to open the file ... I haven't tried that, and would be surprised it if would work, however.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40489993
Hi,
Just wonder why would it be different then any other program (MS Word for example), that asks you if you want to open as read only?
In my case, users must download and save file, then open access and do whatever..
Mainly the user itself is the one who has it open (forgot to close), how can I have the program closing it for them?
Thanks,
Ben
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40490683
This is a known issue or misbehaviour if you like.

There really is no other way than rethinking the concept. For example, when the user has finished editing the data in question, provide a macro for the user to call that will copy these data to another workbook (or export in some other format) and then let your Access application link or import the copy.

/gustav
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40492502
Hi Gustav,

There is no editing here, the story is as follows, user downloads from web a csv file and saves this in a local folder, then opens access and imports records from there.
But when user opens that file first with excel (for whatever reason), and don't close it then the import procedure fails.

Thanks,
Ben
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40492518
Oh, I see. Then I would provide the Excel users with a macro that opens the file, imports the data, and closes the file.
Simple to do, easy for the users, and you will be a star (and they will ask for more).

/gustav
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40492542
The portion of opening and importing I already have it, what I am missing is just the command to close the file, in case user had it open (before the import process takes place) so it does not disrupt the process,
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40492559
That sounds like an educational task.

Or perhaps you could expand the macro to - via VBA - first make a copy of the csv file. If that fails, prompt the user to close the file.

By the way. I just made a test using Excel to open a CSV file. Then I was able to copy the file in Windows Explorer. So I guess you could - via VBA - in Access first create a copy of the file, then import it. Then it doesn't matter if an Excel user has the original open or not.

/gustav
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 3

Author Comment

by:bfuchs
ID: 40492665
ok, the copy option sounds good, will see how it works & let you know.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40492700
I tried the FileCopy command from VBA while the file was open and got permission denied error.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40493257
Then use Shell to call a DOS copy:

strSource = Chr(34) & "F:\Imports\filemaster.csv" & Chr(34)
strDestination = Chr(34) & "F:\Imports\filecopy.csv" & Chr(34)
Shell "cmd /c copy " & strSource  & " " & strDestination  & ""

/gustav
0
 
LVL 84
ID: 40493528
I've had similar issues with Excel in some of my .NET programs which import from Excel sheets. As Gustav indicates, it's generally just a training issue. After the users see the "You must close the Excel workbook" message a few times, they tend to get the picture.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40494736
@Scott,
the problem is that I have many users and the first thing they do is complain to the manager program is not working...this is what I was trying to avoid.
0
 
LVL 3

Author Closing Comment

by:bfuchs
ID: 40494748
that actually worked, Thank you!
just wonder why are dos shell different then windows commands, and perhaps thru DOS there is a way to open the original document as read only?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40495561
You can also copy the file manually with Windows Explorer, so it is FileCopy that is special - it probably, as a precaution, tries to open the file for writing before copying.

/gustav
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now