Solved

open recordsed of excel file read only issue

Posted on 2014-12-09
14
232 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
14 Comments
 
LVL 85
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 4

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 50

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

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 50

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 4

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 50

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
 
LVL 4

Author Comment

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

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 50

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 85
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 4

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 4

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 50

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

726 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