File Opening in Read only format
File Opening in Read only format
File Opening in Read only format
File Opening in Read only format
File Opening in Read only format
File Opening in Read only format File Opening in Read only format File Opening in Read only format File Opening in Read only format File Opening in Read only format File Opening in Read only format File Opening in Read only format File Opening in Read only format
File Opening in Read only format File Opening in Read only format
File Opening in Read only format
Go Back  Xtreme Visual Basic Talk > > > File Opening in Read only format


Reply
 
Thread Tools Display Modes
  #1  
Old 10-06-2006, 07:19 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default File Opening in Read only format


Hi!

I have a strange problem where when I open my file an exception will occur in the code following it because the file is opened as a read only using the openFileDialog1.OpenFile(). However, if I stop my code and wait for the read-write dialog box to pop up in my file and then select this and then proceed with the execution of my file that works fine.

So, in realising that, I decided to try something else, see code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim openFileDialog1 As New OpenFileDialog()

openFileDialog1.InitialDirectory = "G:\Sita Account\Sprint - UPS\UPS Program\"
openFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*"
openFileDialog1.FilterIndex = 2
openFileDialog1.RestoreDirectory = True

If openFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
Try
Fullname = openFileDialog1.FileName
If (Fullname IsNot Nothing) Then
wb = oApp.Workbooks.Open(openFileDialog1.FileName)
End If
oApp.Application.Visible = True
Catch Ex As Exception
MessageBox.Show("Cannot read file from disk)
End Try
End If

And this means that the file opens fine and no read-write dialog pops up (which I assume means the file is already read-write?) but the code still catchs on the following code:

wsData.Select()

(this is a worksheet in the file and that code seems to be fine). So everything seems to be fine except there must still be some kind of read only access being granted?

I've seens posts here about opening files but didn't see any mention of this kind of problem. Any ideas?
Reply With Quote
  #2  
Old 10-06-2006, 09:43 AM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

Quote:
So everything seems to be fine except there must still be some kind of read only access being granted?
You should use the parameter ReadOnly in the Open statement:

Code:
Dim xlTest As New Excel.Application Dim xlwbBook As Excel.Workbook xlwbBook = CType(xlTest.Workbooks.Open(Filename:="c:\test.xls", ReadOnly:=True), Excel.Workbook) Dim xlwsSheet As Excel.Worksheet = CType(xlwbBook.Worksheets(1), Excel.Worksheet) With xlTest .Visible = True .UserControl = True End With xlwsSheet = Nothing xlwbBook = Nothing xlTest = Nothing

In Excel You should see the status within two brackets [] close to the workbook's name.
__________________
Kind regards,
Dennis

.NET & Excel | 2nd edition PED | MVP
Reply With Quote
  #3  
Old 10-10-2006, 05:53 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default

Hi Dennis,

Using

With oApp
.Visible = False
.UserControl = True
.DisplayAlerts = False
End With

seems to have solved one of my problems. Using .UserControl = True means my application doesn't hang now.

But the problem I have is that I want the application to be read-write but it appears to be read only.

What happens is that I run my application (which is not visible) and then because I'm using the openfiledialog box (which I suppose I'm not using correctly) after a few moments a dialog box pops up saying that my file is now available for editing.

If I click on this while my application is still running the excel file becomes visible and the display alerts = false no longers works. So that's no good.

If I wait until the end to click on the available for editing dialog box then at the very end a saveAs dialog box pops up (which it shouldn't because I have the code wb.Close(SaveChanges:=True) and this code works properly normally). So, if I click saveAs and overwrite my file this does nothing. My file is not saved. Then, if I click on the available for editing dialog box which is still present another saveAs dialog box pops up. If I choose saveAs here and overwrite my file, then it is saved. But, obviously I don't want to have to do this manually. So, I'm not sure what's happening there? Anyone got an idea?

thanks!

JF
Reply With Quote
  #4  
Old 10-16-2006, 08:03 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hey JF,

I'm going to guess here that you are running tests and creating hanging instances. These hanging instances can be found within the Task Manager and closed manually. (You can also close them prgrammatically with .NET's Process Class.) However, I think it's best if we get to the root of the problem.

To test this, I think you should close out all visible instances of Excel and then go into the Task Manager (hit Ctrl+Alt+Delete) to make sure that no Excel instances are running behind the scenes. Look for "Excel.exe" and close them if you have to. Then run your routine. My guess is that the 1st time you run it, you can open your file on a read-write basis, but subsequent tests will create the read-only notification effects that you are talking about. I also suspect that you'll have instances hanging within the Task Manager.

Can you test this out for us? If this is the case, we'll try to talk you through closing down your application cleanly so that the Excel application instance does not hang.

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #5  
Old 10-16-2006, 01:05 PM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default

Hi Mike,

Thanks for your input. I see what you're saying but I think everythings running fine. I usually have the task manager open so that I can remove hanging instances if I'm debugging and an error means my code doesn't get to the 'cleanup' section.

The cleanup seems to work fine also (since when the code runs to completion there's no hanging instances). So that doesn't seem to be the problem I don't think.

Everything runs fine from start to finish, but for some reason a 'read only' dialog box pops up at some point (as would happen say if someone else had the file open). The program can perform all it's tasks but it can't save the file until the 'read only' dialog box is pressed so as to make the file read-write. And this needs to be done manually. If this is pressed during the running of the program then this makes the file visible which I don't want. If not pressed at all then nothing will be saved until it is pressed and saved manually. Strange?

I can only guess it's a problem with the file or the way it's opened?

Sometimes also when I open the file manually I can't see it (only the task bar) and so I have to run a macro to make it visible (application.visible = true). This doesn't seem to be a related problem but it could be. I don't see why it would happen since I set everything back at the end:

With oApp.Application
.Visible = True
.ScreenUpdating = True
.UserControl = True
.DisplayAlerts = True
End With

(Also... sorry for being cheeky :-) but while I have you here... is this good code or bad code:

reference = rngData.Range(SERVICE & x).Value.ToString

where rngData is cell(1,1) as a range object. My code is littered with this kind of referencing from cell 1 so as to make the code work with option strict on.)

Thanks a lot, JF
Reply With Quote
  #6  
Old 10-16-2006, 06:14 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Quote:
Originally Posted by JFitz15
I can only guess it's a problem with the file or the way it's opened?
If you have no hanging instances, then I think you might have a corruption or something with the file. Not sure. I would definately try opening it manually and see what happens, but then again it looks like you've tried that:

Quote:
Sometimes also when I open the file manually I can't see it (only the task bar) and so I have to run a macro to make it visible (application.visible = true).
I don't understand what you mean here? If you opened it "manually" then the excel instance was already visible. Are you saying that upon opening this file, your Excel application disappears on you? And then when you run a macro to restore it, how do you run that code (if Excel is invisible at that time) - are you running Automation code using GetObject() then? This all sounds *extremely* odd -- does this file have any macros within it that are running when opened?


Quote:
(Also... sorry for being cheeky :-) but while I have you here... is this good code or bad code:

reference = rngData.Range(SERVICE & x).Value.ToString

where rngData is cell(1,1) as a range object. My code is littered with this kind of referencing from cell 1 so as to make the code work with option strict on.)
This is workable, obviously, but Worksheet.Range("A" & i) is kind of ugly when compared to Worksheet.Cells(r, c). But, yes, Worksheet.Cells(r, c) does trip over 'Option Strict' because this method returns a Range weak-typed 'As Object'. This is a bummer in VB6 and VBA as well because we lose IntelliSense on the memeber listing for the returned Range.

The ".NET way", of course, would be to use CType(), something like this:
Code:
theValue = CType(rngData.Cells(1,1), Excel.Range).Value
But this is rather verbose.

So here's my personal solution, and remember, you heard it here first .
I place the following code in all my projects:
Code:
Public Shared Function Cxl(ByVal xlApp As Object) As Excel.Application Return CType(xlApp, Excel.Application) End Function Public Shared Function Cwb(ByVal xlWB As Object) As Excel.Workbook Return CType(xlWB, Excel.Workbook) End Function Public Shared Function Cws(ByVal xlWS As Object) As Excel.Worksheet Return CType(xlWS, Excel.Worksheet) End Function Public Shared Function CRng(ByVal xlRng As Object) As Excel.Range Return CType(xlRng, Excel.Range) End Function
Now we can cleanly convert anything with far less verbose code. So to cast to a range, you could use CRng() instead of CType(,Excel.Range). For example:
Code:
theValue = CRng(rngData.Cells(1,1)).Value
Ok, maybe this won't change the world... But I find it clean, neat, and much easier to read. And Option Strict is saticefied and IntelliSense is fully operational. (I do this for VB6 and VBA code as well, btw.)

Well that's it for today's lesson.

Let us know if there's anything else on that weirdo Workbook of yours?? A very odd thing indeed...

Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #7  
Old 10-17-2006, 07:21 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default

he he! thanks for all that.

Humm, some kind of file corruption. That's probably the most logical explanation... I'll look into it.

Well, I just opened my file there manually anyway and it opened fine. But I'll cut and paste into another workbook and see if that changes anything.

When I said I open it manually but can only see the taskbar what I should have said was that when I open it manually I can only see the menu of excel and not the workbook itself. Since I can see the menu I have a little macro stored in another file which makes everything visible (since sometimes you can run into this kind of problem if you forgot to set visible = true!) and that then makes my file visible. I don't know how this can happen of course since I do set it back to visible in my code...

So you think worksheet.range("A" & i) isn't the best kind of code? Unfortunately I use this everywhere in my vb code so as to find the cells I want to manipulate. What I would do is find references to any number of columns and then the columnref becomes 'A' or 'CW' or whatever and then I just use the range function to change it. This does mean that I have to take out the '$' values however (e.g. from something like $C$W) before I can use it. So you think I should change everything to cells(a,b) and call your cRng function each time. I'd be calling it very often then... would that slow the code a lot?

thanks, JF
Reply With Quote
  #8  
Old 10-17-2006, 08:58 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Your existing code is fine, I wouldn't undo all of this just to make it "cleaner". In fact, you run the risk of making a mistake when converting.

Going forward, however, yes, I would get away from this kind of code. It's not the end of the world here -- it really is fine if you wanted to continue with this -- but I would start changing your thinking.

Mentally you should be thinking of Row's and Columns. This kind of integer index mentality will help you when moving values between Arrays and Ranges. This becomes particularly evident when the Column number is greater than 26. What "letter" is the 100th column? You can make conversion functions (I did in my early VBA days), but it's more complex than one would think. The other way is to make use of Workshee.Cells(1, 100).Address, but (a) this is odd because one would be better off making use of Worksheet.Cells(r, c) directly and (b) you would have the "$" character to deal with, as you described.

In particular, if you were looping, let's look at the following, which basically makes a "multiplication table":
Code:
Dim rng As Excel.Range = ... ' <-- Set 'rng' to something. For r as Integer = 1 to rng.Rows.Count For c as Integer = 1 to rng.Columns.Count CRng(rng.Cells(r, c)).Value = r * c ' <-- Assign Rng.Value = ... Next c Next r
Ok, now if one is uncomfortable with rng.Cellls(), we could use a For-Each loop:
Code:
Dim rng As Excel.Range = ... ' <-- Set 'rng' to something. For Each cell As Excel.Range In rng cell.Value = cell.Row * cell.Column Next cell
The above looks even cleaner, although this is not my personal prefernence. (But, boy, it does look clean, eh?) A For-Each loop will run a *tad* slower, and accessing the rng.Row and rng.Column property within each loop also has a small cost, but looping through cells using Automation is so painfully slow (especially when using .NET code) that you'd never, ever notice. So if the for-each loop looks cleaner, then fine.

The third version, I won't even show because it would be too messy. The "third" version is your way: converting the column to a letter (or pair of letters) and then concatinating with the row number. In terms of execution, this is a *LOT* of extra overhead. String manipulations are expensive, period. All this conversion to letters, and then forcing the .Range() function to parse the string to figure out the column and range number -- when you knew these values in advance! -- is very expensive. That said, looping through the cells of a range is so painfully expensive that I think you'd never notice this effect either.

So why care if one would never notice the execution speed? Because I think you'll grow as a programmer if you start to "think right". In this case, computers think in terms of Integer offsets, period. For everything, actually. So if you start thinking the same way, you'll make code that runs more cleanly and is actually easier to read, because there will be less conversion code back and forth from Integers to/from Strings. And although you will not generally notice any speed improvement when looping through cells of a Range, in virtually every other programming context you will have a noticeable speed impovement if you keep your code looping with Integers and avoiding Strings if at all possible.


Quote:
Originally Posted by JFitz15
When I said I open it manually but can only see the taskbar what I should have said was that when I open it manually I can only see the menu of excel and not the workbook itself. Since I can see the menu I have a little macro stored in another file which makes everything visible (since sometimes you can run into this kind of problem if you forgot to set visible = true!) and that then makes my file visible. I don't know how this can happen of course since I do set it back to visible in my code...
Ah, so the Workbook is hidden, not the Excel application? Is it 'Hidden' or 'VeryHidden'? Can you see it within the VBA IDE (I suspect yes). I would then open the Properties window for the 'ThisWorkbook' class within that project and see what the Visibility setting is at. I would think that changing the Workbook's visibility is the key here, not the Application.Visible setting. Yet, it is the Application.Visible setting that you use to restore it? But the Application itself is actually always visible, right??

Again, all very, very strange...
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 10-17-2006 at 03:30 PM.
Reply With Quote
  #9  
Old 10-17-2006, 10:14 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default

Thanks for the advice... I'll change my approach I might end up in a cell myself if I'm not careful... ugh, sorry...

Well I've checked everything and all the sheets are set to visible. I don't see that option in the 'thisworkbook' properties tab though so can't change anything there. Ran the code again and everything worked fine and I could open up manually afterwards so doesn't seem to be repeating it's invisibility trick but I'll have to keep and eye out...

...however... the infamous 'read only' dialogue box is still poping up which I have to click manually at some point while my application is running.

So, do I just have to live with this? The properties of the file are not 'read only' so I can only guess that when the file is opened the application is momentarily taking it's read-write privalages (i.e. just as another person might have these if they opened the file before you), or these are taken somehow when the file is opened, but at some point (at the beginning) it returns the read-write privalages to the file so that the user can now click the read-write dialog box... just as would happen when the person who first opened the file now closes it and the read-write dialog box will pop up for the second user? It's not the end of the world I guess but I'd rather fix it if possible? Kind of seems the displayalerts = false isn't quite working as well?

Ah well!
Reply With Quote
  #10  
Old 10-17-2006, 10:27 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

I am becomming more and more convinced that you have hanging instances in your Task Manager. There is a delay before one of your previous Excel instances actually gets cleaned up by the Garbage Collector and so when you run it again the next time, you are not given read-write privledges... Until some seconds later when your previous Excel instance is finally disposed of. At this point you get the "read-write dialog box" indicating that the file is not available for writing, and if you look in the Task Manager, there is nothing there except for the one Excel instance because the *other* instance is now GONE! (This is why you got access to this file again -- when the other, handging instance finally released and disappeared from the Task Manager.)

At a minimum I think you need to add GC.Collect() and GC.WaitForPendingFinalizers() to your final cleanup routine. But you'll likely need more than just that. (Although that will be a big start!) Have a look at this post on Cleaning up your COM Objects and adapt it to your cleanup routine.

If you want, post your existing cleanup routine here and we can show you how to adjust it. But give that post/thread a read first and see if it makes sense to you. Just ask if anything is unclear...

,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #11  
Old 10-17-2006, 11:25 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default

Well I can only say what I see

I ran it twice there and what happens is that there is no EXCEL.EXE in the task manager... that's what I'm looking for.

When the open file dialog box comes up an EXCEL.EXE instance comes into the task manager and after a moment the 'available for editing' dialog box comes up which I click on at the end. I have to do 'save as' twice... the first one is asked by the program (which does nothing because I haven't pressed the available for editing dialog box) and the second one is asked after I press the 'available for editing' dialog box and then the EXCEL instance disappears from the task manager after I click close the application (that's specifically when I press the close button, the x). And then there is no EXCEL.EXE present.

I did notice however that if I cancel the save changes box then EXCEL.EXE does stay in the task manager but otherwise it doesn't.

I've read all the tutorials and I think my closing routine is ok (although I noticed I wasn't cleaning up all my range objects which I've done now... with no change to program)... oApp contains two workbooks one called wb and one called wbControlFile.

Dim oApp As New Excel.Application
Dim wb, wbControlFile As Excel.Workbook
Dim rngData, rngAccess, rng1, rngStatus, rngCopy As Excel.Range
Dim wsData, wsAccess, wsCopy, wsSmartbill, wsDataCopy As Excel.Worksheet

cleanup:
If Not oApp Is Nothing Then
GC.Collect()
GC.WaitForPendingFinalizers()

If Not rngData Is Nothing Then
Marshal.FinalReleaseComObject(rngData)
rngData = Nothing
End If

If Not rngAccess Is Nothing Then
Marshal.FinalReleaseComObject(rngAccess)
rngAccess = Nothing
End If

If Not rng1 Is Nothing Then
Marshal.FinalReleaseComObject(rng1)
rng1 = Nothing
End If

If Not rngStatus Is Nothing Then
Marshal.FinalReleaseComObject(rngStatus)
rngStatus = Nothing
End If

If Not rngCopy Is Nothing Then
Marshal.FinalReleaseComObject(rngCopy)
rngCopy = Nothing
End If

If Not wsData Is Nothing Then
Marshal.FinalReleaseComObject(wsData)
wsData = Nothing
End If

If Not wsAccess Is Nothing Then
Marshal.FinalReleaseComObject(wsAccess)
wsAccess = Nothing
End If

If Not wsCopy Is Nothing Then
Marshal.FinalReleaseComObject(wsCopy)
wsCopy = Nothing
End If

If Not wsSmartbill Is Nothing Then
Marshal.FinalReleaseComObject(wsSmartbill)
wsSmartbill = Nothing
End If

If Not wsDataCopy Is Nothing Then
Marshal.FinalReleaseComObject(wsDataCopy)
wsDataCopy = Nothing
End If

If Not wb Is Nothing Then
If saveDocuments = False Then
wb.Close(SaveChanges:=True)
Else : wb.Close(SaveChanges:=True)
End If
Marshal.FinalReleaseComObject(wb)
wb = Nothing
End If
If Not wbControlFile Is Nothing Then
wbControlFile.Close(SaveChanges:=False)
Marshal.FinalReleaseComObject(wbControlFile)
wbControlFile = Nothing
End If
oApp.Quit()
Marshal.FinalReleaseComObject(oApp)
oApp = Nothing
End If
End Sub
Reply With Quote
  #12  
Old 10-17-2006, 12:24 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

That cleanup routine looks outstanding. Just one or two minor pointers however:

(1) I think you made a goof here:
Code:
If saveDocuments = False Then wb.Close(SaveChanges:=True) Else : wb.Close(SaveChanges:=True) End If
To make it easier on yourself, I think you can simply call:
Code:
wb.Close(SaveChanges:=saveDocuments)

(2) After your final aApp.Quit(), etc. I would again add these two lines:
Code:
GC.Collect() GC.WaitForPendingFinalizers()
I can't say if this will work for sure and it technically shouldn't be necessary, but, well, it definately cannot hurt. It might do the trick. (The idea is to get the GC to release anything that might be hanging immediately, not wait for some time later when the GC might actually collect again.) But do NOT get rid of these same two lines that you have at the top of your cleanup routine; the ones at the top are critical. This 2nd set is, well, optional, but I'm hoping it might do it for you...

If this doesn't do it, I think you'll have to test this with a brand-new Workbook and see if you get the same effects. I think you'll need to do this to discern if this is a problem with the Workbook itself or with your code.

That's all my thoughts at this juncture!

Good luck, and keep us posted...
,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 10-17-2006 at 03:28 PM.
Reply With Quote
  #13  
Old 10-18-2006, 04:40 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default

Well, since the problem occurs the first time I run the program and straight after I've logged on I still think it wouldn't be a hanging problem since I haven't run anything yet. But I'm no expert so anything could be happening as far as I'm concerned ! I added the GC lines to top and bottom now but without a change. Also, the invisibility trick has returned inexplicably! I had to run my application.visible macro to see the file when I opened it manually .

Regarding the wb.Close(SaveChanges:=True) I just change that back and forth when I'm debugging depending on whether I want the file to save or not, so in this case I wanted it to save regardless.

I cut and pasted the code into a brand new workbook already but with the same consequences so all in all I'd be suspicious that it has something to do with how I'm opening the file.... using the open file dialog box. In fact, I'd be fairly certain about this because I open two workbooks. The other workbook I just open using the simple statement

wbControlFile = oApp.Workbooks.Open("G:\UPS Program Control file.xls")

and there's no problem at all with this. It is the other one which is opened via the openfiledialog which causes the problem. So it seems likely that the problem is there somehow...

Anyway... I'll probably figure it out eventually... or just ask the user to click on the dialog box if needs be...

Thanks for your help Mike! And if you come up with any more ideas... let me know!!

JF
Reply With Quote
  #14  
Old 10-18-2006, 05:17 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default

I guess the most sensible thing is to try

wb = oApp.Workbooks.Open("G:\AStest.xls") and
wbControlFile = oApp.Workbooks.Open("G:\UPS Program Control file.xls")

and that works perfectly without any read-write dialog boxes coming up so I guess that proves my suspicions correct. It has to be because I'm opening using the openfiledialog. So again... my code for that is below. Is there some field that you need to populate to specify clearly that it should be opened as read-write, or something like that?

Private Sub btnRateCharges_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRateCharges.Click


Dim myStream As IO.Stream = Nothing
Dim openFileDialog1 As New OpenFileDialog()
Dim openMyFile As IO.FileStream

openFileDialog1.InitialDirectory = "G:\"
openFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*"
openFileDialog1.FilterIndex = 2
openFileDialog1.RestoreDirectory = True

If openFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
Try
myStream = openFileDialog1.OpenFile()
If (myStream IsNot Nothing) Then
wb = oApp.Workbooks.Open(openFileDialog1.FileName)
End If
With oApp
.Visible = False
.UserControl = True
.DisplayAlerts = False
End With
Catch Ex As Exception
MessageBox.Show("Cannot read file from disk. Original error: " & Ex.Message)
Finally
If (myStream IsNot Nothing) Then
myStream.Close()
End If
End Try
End If
Reply With Quote
  #15  
Old 10-18-2006, 07:02 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default

Solved the problem by creating my own dialog box using drive listbox, dirlistbox and filelistbox. Works perfectly. No read-write problems. What a slog that was!

I'd prefer to use the microsoft dialog boxes but no matter.

OK thanks so much for all your help and the good advice

JF
Reply With Quote
  #16  
Old 10-18-2006, 10:00 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Wow, sorry you had to go through all that...

So clearly it's the OpenFileDialog that's the issue, although that would seem impossible on the surface. There is no reason that
Code:
wb = oApp.Workbooks.Open(openFileDialog1.FileName)
should be any different than calling
Code:
wb = oApp.Workbooks.Open("G:\UPS Program Control file.xls")
So my first thought was that there could be some wacky string incompatibility here such as Workbooks.Open() expecting ASCII strings and the OpenFileDialog returning Unicode? This didn't sound right, but it was all I could think of...

But then I looked at your code a little closer. It looks like you are attempting to open the file *twice*. The first time via 'openFileDialog1.OpenFile()' and the second time via 'Workbooks.Open()':

Code:
If openFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then Try myStream = openFileDialog1.OpenFile() If (myStream IsNot Nothing) Then wb = oApp.Workbooks.Open(openFileDialog1.FileName)
Now I don't know what happens exactly upon the first call to 'openFileDialog1.OpenFile()'. I guess it finds a running instance of Excel and opens it there. I don't know why this might cause the "invisibility" effects that you were getting, but this would be my guess as to the culprit. But it definately should explain the "file locked" scenario. And I'm guessing that when you later get to 'myStream.Close()' is where the file is released and you get the "File is available for read-write" message.

I think the solution would be to stick with the 'Workbooks.Open()' approach exclusively, right?

Or is there a purpose to using a FileStream as well?

Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #17  
Old 10-18-2006, 11:20 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default

Yep at first glance that appears to have solved the problem.

I didn't really know how to use the opendialogbox when I first tried so I went on the web and looked for info on how to use it which is where I got that code. I didn't really understand it so I just assumed that that was the correct way to open the file!

I don't really know what the function of the io.stream is but it appears to be the problem. The code becomes available for editing half way through the running of the application.

I don't think I'd ever have solved that one!
Reply With Quote
  #18  
Old 10-18-2006, 12:19 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Quote:
Originally Posted by JFitz15
Yep at first glance that appears to have solved the problem.
Yeah, I'm really sorry I did not notice that one earlier... It did seem like some sort of Excel mystery involving hanging instances. I had not looked at your code carefully enough. My bad.

Quote:
I didn't really know how to use the opendialogbox when I first tried so I went on the web and looked for info on how to use it which is where I got that code. I didn't really understand it so I just assumed that that was the correct way to open the file!
I would generally use the OpenFileDialog to simply get the string result from the user. That is, call openFileDialog1.ShowDialog() and then utilize the openFileDialog1.FileName(). That's pretty much all you need to know. If you utilize openFileDialog1.Open() then you are opening it as a Text file. This is fine if your file actually is a Text file! That is, you could open an XLS file as Text, say within Notepad.exe, but you'll get a complete mess. You could even edit it, but that would be almost certain to create a corruption in the file. So, I would use the OpenFileDialog to get the .FileName only, and then use whatever is appropriate to open that file using that .FileName and or Path.

The other curious thing you have here is this:
Code:
openFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*"
Since you expect the user to choose an XLS file, such as: "G:\UPS Program Control file.xls", then I would think that you would want your filter to be "*.xls", right? Something like this:
Code:
openFileDialog1.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*"


Quote:
don't really know what the function of the io.stream is but it appears to be the problem. The code becomes available for editing half way through the running of the application.
A FileStream is how you manipulate Text Files or Binary Files. Here's a quickie overview: http://www.startvbdotnet.com/files/default.aspx

Quote:
I don't think I'd ever have solved that one!
Heh, I almost missed it too! Glad we solved though...

,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #19  
Old 10-19-2006, 04:33 AM
JFitz15 JFitz15 is offline
Regular
 
Join Date: Jul 2006
Posts: 74
Default

Actually both of those filters above show all the files. If you want only excel files you have to omit the 'all files' bit and just have

Code:
openFileDialog1.Filter = "Excel files (*.xls)|*.xls"
Thanks for the help... much needed and much appreciated!

JF
Reply With Quote
  #20  
Old 10-19-2006, 08:52 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Oh, yes, understood... If you didn't have "All Files" as a choice in your original filter then the user would not have been able to find your file at all. Only text files would have been viewable!

Glad you're up and running.
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
File Opening in Read only format
File Opening in Read only format
File Opening in Read only format File Opening in Read only format
File Opening in Read only format
File Opening in Read only format
File Opening in Read only format File Opening in Read only format File Opening in Read only format File Opening in Read only format File Opening in Read only format File Opening in Read only format File Opening in Read only format
File Opening in Read only format
File Opening in Read only format
 
File Opening in Read only format
File Opening in Read only format
 
-->