VB6 and Excel
in [Visual Basic]
|
Prev: Address parsing algorithm needed please
Next: DataGrid
From: Peter T on 26 Apr 2010 06:45 "Peter T" <peter_t(a)discussions> wrote in message > "Stan Weiss" <srweiss(a)erols.com> wrote in message >> The spec.'s keep changing. Now instead of saving the file and exiting >> the program I am letting the user go to work on the spreadsheet after I >> have populated some of the cells. This code works. Do you see any >> possible problems? >> >> oWS.Cells(21, 12) = "Cell 21 - 12 - 3" >> >> oXL.Visible = True 'Make Excel Visible >> oXL.UserControl = True 'Give the User Control >> >> Set oWS = Nothing >> ' oWB.SaveAs ("C:\Stan_Test.xls") >> ' oWB.Close >> Set oWB = Nothing >> >> ' oXL.Quit >> Set oXL = Nothing >> >> Stan > > Couple more thoughts - > > If the intention is ultimately to leave the workbook open and visible to > the user, consider starting by attempting to reference an existing Excel > instance the the user is already working with, eg > > on error resume next > Set oXL = GetObject(, "excel.application") > on error goto 0 ' or other handler > > if oXL is nothing then > ' as you were doing before > Else > check if the app is visible, if not maybe it's been created for some other > programatic needs (yours maybe). Typically though it'll be the user's > working instance. > > might be an idea to check if your workbook is already open > on error resume next > set oWB = oXL.Workbooks("myFile.xlx") > on error goto 0 > > > Another thing to consider, if leaving the newly "created" instance open, > personal.xls(m) will not have loaded and neither will have any addins. If > their absence might confuse the user it'd be easy enough for your code to > open them. > > Regards, > Peter T Sorry, I see MikeD had already suggested starting with GetObject (but only do that if the ultimate intention is to leave it visible) My second though is still valid. Peter T
From: Stan Weiss on 26 Apr 2010 11:13 MikeD wrote: > > "Stan Weiss" <srweiss(a)erols.com> wrote in message > news:4BD339C0.41110549(a)erols.com... > > The spec.'s keep changing. Now instead of saving the file and exiting > > the program I am letting the user go to work on the spreadsheet after I > > have populated some of the cells. This code works. Do you see any > > possible problems? > > > > oWS.Cells(21, 12) = "Cell 21 - 12 - 3" > > > > oXL.Visible = True 'Make Excel Visible > > oXL.UserControl = True 'Give the User Control > > > > Set oWS = Nothing > > ' oWB.SaveAs ("C:\Stan_Test.xls") > > ' oWB.Close > > Set oWB = Nothing > > > > ' oXL.Quit > > Set oXL = Nothing > > > > Stan > > You might not want to set your references to Nothing unless/until you really > no longer need them in your program. Of course, if they're local variables, > they'll automatically be set to Nothing when the procedure goes out of > scope. > > However, what you really need to do is test on several different end-user > PCs. Sure, we might be able to point out this or that, but that shouldn't > replace actual "real-world" testing. > > -- > Mike > > Mike, After control is turned over to the user the program ends/unloads itself. I have data that was entered by the end user and validated. The only reason the end user does not enter the information directly into the spreadsheet is that there is company proprietary information in the spreadsheet that they can not see. Rather then reenter the data into the spreadsheet with the possibility of causing new errors, I am just taking the validated data and populating these cells in the spreadsheet. Stan
From: GS on 26 Apr 2010 12:05 > After control is turned over to the user the program ends/unloads > itself. I have data that was entered by the end user and validated. The > only reason the end user does not enter the information directly into > the spreadsheet is that there is company proprietary information in the > spreadsheet that they can not see. Rather then reenter the data into the > spreadsheet with the possibility of causing new errors, I am just taking > the validated data and populating these cells in the spreadsheet. > Stan So what prevents the user from seeing the company's proprietary data if he's left working in that spreadsheet? A few suggestions... This sounds like a corporate environment you're building this project for. If I understand the evolution of your project correctly, you now have the user enter data (how? ..into a form perhaps?) where you first validate that data before writing it to the worksheet. So why bother opening the file at all?<g> Everything you describe you're doing here can be done with ADO without need for anyone to access the workbook. If you password protect the file (via the Save As dialog) from being viewed by unauthorized people, you could include an "admin" login mechanism in your project to allow only authorized people to open the workbook. This leaves the only changes allowed to be entered are those validated by your app OR by people with the proper level of authorized access. Your login mechanism can determine who gets "Read Only" access and who gets full access. Now your app is not dependant on whether the machine it runs on having either Excel installed or the workbook stored on a local drive. The workbook can be stored on a network share where any number of access level scenarios can be programmed for. If users need to have the workbook open because they require access to only specific worksheets in the workbook, you could manage that using your own single automated instance of Excel that can be locked down however you want so the user only has access to the Excel features/functionality that are necessary for the task at hand. Optionally, if you need users to use their instance of Excel you could make your project a COMAddin (ActiveX DLL) and set it up to manage the UI how you want when your file is the ActiveWorkbook, restoring the UI to its former state when the user activates another file. Garry --
From: Stan Weiss on 26 Apr 2010 12:39
GS wrote: > > > After control is turned over to the user the program ends/unloads > > itself. I have data that was entered by the end user and validated. The > > only reason the end user does not enter the information directly into > > the spreadsheet is that there is company proprietary information in the > > spreadsheet that they can not see. Rather then reenter the data into the > > spreadsheet with the possibility of causing new errors, I am just taking > > the validated data and populating these cells in the spreadsheet. > > Stan > > So what prevents the user from seeing the company's proprietary data if > he's left working in that spreadsheet? The person who originally enters the data will not the same person who is working with the spreadsheet. In fact they will not even at the same location. If this works out as planed there will be many people entering information but only 1 or 2 people that will work with the information and spreadsheet. As can be seen this is work in progress and the specs could change tomorrow, or has happened before this project could get dropped. Thanks, Stan > > A few suggestions... > This sounds like a corporate environment you're building this project > for. If I understand the evolution of your project correctly, you now > have the user enter data (how? ..into a form perhaps?) where you first > validate that data before writing it to the worksheet. So why bother > opening the file at all?<g> > > Everything you describe you're doing here can be done with ADO without > need for anyone to access the workbook. If you password protect the > file (via the Save As dialog) from being viewed by unauthorized people, > you could include an "admin" login mechanism in your project to allow > only authorized people to open the workbook. This leaves the only > changes allowed to be entered are those validated by your app OR by > people with the proper level of authorized access. Your login mechanism > can determine who gets "Read Only" access and who gets full access. Now > your app is not dependant on whether the machine it runs on having > either Excel installed or the workbook stored on a local drive. The > workbook can be stored on a network share where any number of access > level scenarios can be programmed for. > > If users need to have the workbook open because they require access to > only specific worksheets in the workbook, you could manage that using > your own single automated instance of Excel that can be locked down > however you want so the user only has access to the Excel > features/functionality that are necessary for the task at hand. > > Optionally, if you need users to use their instance of Excel you could > make your project a COMAddin (ActiveX DLL) and set it up to manage the > UI how you want when your file is the ActiveWorkbook, restoring the UI > to its former state when the user activates another file. > > Garry > -- |