Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding a file in excel
I think is going to be a hard one. I know Excel 2003 and newer has a file (it
is called Excel11.xlb on my computer). In this file Excel saves what toolbars were visible the last time Excel was shut down. The problem is that I want to open this file when I am closing a program I have developed. By opening this file I will restore the toolbars various users have setup on their Excel. I can already restore built in toolbars, but I want to restore custom toolbars if any user has set made their own. Excel knows where this file is located because in accesses it when excel is opened. I know where it is on my computer, but I can not go around to every ones computer and find the file and map the location. And I am sure it is not the same name on every computer. So, if Excel knows where it is, how do I make my program call this file right before the user closes their program. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding a file in excel
By default, Excel stores any custom toolbars in the Personal.xls workbook,
which is stored under the general startup folder that any user can get to. This workbook is also generally hidden as it's hidden by default. However, the user can attach such toolbars to other workbooks. For Excel startup locations (1 for all users, 1 for the specific user, and 1 alternate startup location that the user specifies under the General tab of the Options dialog box), you may want to look up article 822107 http://support.microsoft.com/kb/822107 You may also want to take a look at the list of the various ENVIRON function enumerators, which will help in mapping to the user's Document and Settings profile http://www.utteraccess.com/forums/pr...&type=thr ead The other file that I see for Excel, which I'm not sure what the pip extension is for, but anyhow, it seems to be pertanent to the user settings is the following: C:\Documents and Settings\<username\Application Data\Microsoft\Office\Excel10.pip (Note, this is for Excel 2002) The file path you refering to is: C:\Documents and Settings\<username\Application Data\Microsoft\Excel\Excel10.xlb (Again, this is Excel 2002) Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "Josh C" wrote in message ... I think is going to be a hard one. I know Excel 2003 and newer has a file (it is called "Excel11.xlb" on my computer). In this file Excel saves what toolbars were visible the last time Excel was shut down. The problem is that I want to open this file when I am closing a program I have developed. By opening this file I will restore the toolbars various users have setup on their Excel. I can already restore built in toolbars, but I want to restore custom toolbars if any user has set made their own. Excel knows where this file is located because in accesses it when excel is opened. I know where it is on my computer, but I can not go around to every ones computer and find the file and map the location. And I am sure it is not the same name on every computer. So, if Excel knows where it is, how do I make my program call this file right before the user closes their program. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding a file in excel
Hi Ronald,
here is some code the statement works (i think because it is a .xls file, i tried a .doc file). i want to open the Excel11.xlb file. if you have any suggestion that would be great thanks 'want to open .xlb 'doesn't work 'Workbooks.Open ("C:\Documents and Settings\<username\Application Data\Microsoft\Excel\Excel11.xlb") 'Workbooks.Open ("C:\Documents and Setting\JoshChandler\Application Data\Microsoft\Excel\Excel11.xlb") 'does work 'Workbooks.Open ("M:\RalcoProdApp\SL - Sales\SL002 Ultimate Calculator\Names.xls") "Ronald Dodge" wrote: By default, Excel stores any custom toolbars in the Personal.xls workbook, which is stored under the general startup folder that any user can get to. This workbook is also generally hidden as it's hidden by default. However, the user can attach such toolbars to other workbooks. For Excel startup locations (1 for all users, 1 for the specific user, and 1 alternate startup location that the user specifies under the General tab of the Options dialog box), you may want to look up article 822107 http://support.microsoft.com/kb/822107 You may also want to take a look at the list of the various ENVIRON function enumerators, which will help in mapping to the user's Document and Settings profile http://www.utteraccess.com/forums/pr...&type=thr ead The other file that I see for Excel, which I'm not sure what the pip extension is for, but anyhow, it seems to be pertanent to the user settings is the following: C:\Documents and Settings\<username\Application Data\Microsoft\Office\Excel10.pip (Note, this is for Excel 2002) The file path you refering to is: C:\Documents and Settings\<username\Application Data\Microsoft\Excel\Excel10.xlb (Again, this is Excel 2002) Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "Josh C" wrote in message ... I think is going to be a hard one. I know Excel 2003 and newer has a file (it is called "Excel11.xlb" on my computer). In this file Excel saves what toolbars were visible the last time Excel was shut down. The problem is that I want to open this file when I am closing a program I have developed. By opening this file I will restore the toolbars various users have setup on their Excel. I can already restore built in toolbars, but I want to restore custom toolbars if any user has set made their own. Excel knows where this file is located because in accesses it when excel is opened. I know where it is on my computer, but I can not go around to every ones computer and find the file and map the location. And I am sure it is not the same name on every computer. So, if Excel knows where it is, how do I make my program call this file right before the user closes their program. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding a file in excel
Ronald
Incorrect information on later versions. Excel stores all Menu and Toolbar customizations in the *.XLB file. I believe you are thinking of pre-xl97 when Excel had the old menu editor and stored the customizations in Personal.xls Gord Dibben MS Excel MVP On Thu, 3 May 2007 16:19:12 -0400, "Ronald Dodge" wrote: By default, Excel stores any custom toolbars in the Personal.xls workbook, which is stored under the general startup folder that any user can get to. This workbook is also generally hidden as it's hidden by default. However, the user can attach such toolbars to other workbooks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding a file in excel
Josh
I don't believe you should be messing around with anyone's Excel *.xlb file. Create the toolbars when your application opens. When your application closes, delete the toolbars and restore the user's original setup without affecting user's *.xlb file Sub Add_Controls() Dim i As Long Dim onaction_names As Variant Dim caption_names As Variant onaction_names = Array("macro1", "macro2", "macro3") caption_names = Array("caption 1", "caption 2", "caption 3") With Application.CommandBars("Cell") For i = LBound(onaction_names) To UBound(onaction_names) With .Controls.Add(Type:=msoControlButton) .OnAction = ThisWorkbook.Name & "!" & onaction_names(i) .Caption = caption_names(i) End With Next i End With End Sub Sub Delete_Controls() Dim i As Long Dim caption_names As Variant caption_names = Array("caption 1", "caption 2", "caption 3") With Application.CommandBars("Cell") For i = LBound(caption_names) To UBound(caption_names) On Error Resume Next .Controls(caption_names(i)).Delete On Error GoTo 0 Next i End With End Sub For more on this see Debra Dalgleish's site. http://www.contextures.on.ca/xlToolbar02.html John Walkenbach has a download named MenuMakr which you can incorporate into your workbook for creating custom menus. http://www.j-walk.com/ss/excel/tips/tip53.htm Gord Dibben MS Excel MVP On Thu, 3 May 2007 12:14:01 -0700, Josh C wrote: I think is going to be a hard one. I know Excel 2003 and newer has a file (it is called Excel11.xlb on my computer). In this file Excel saves what toolbars were visible the last time Excel was shut down. The problem is that I want to open this file when I am closing a program I have developed. By opening this file I will restore the toolbars various users have setup on their Excel. I can already restore built in toolbars, but I want to restore custom toolbars if any user has set made their own. Excel knows where this file is located because in accesses it when excel is opened. I know where it is on my computer, but I can not go around to every ones computer and find the file and map the location. And I am sure it is not the same name on every computer. So, if Excel knows where it is, how do I make my program call this file right before the user closes their program. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding a file in excel
I have seen that happen in XL97, XL2000, and XL2002, but can't say for
XL2003 or later. Even though I have used earlier versions of Excel, but I never did use Excel extensively until Excel 97, which I hated Excel 97 cause of all the different issues that I ran into with Excel 97. As far as that's concerned, I would have rather worked with Lotus 1-2-3, V2.3 than Excel 97, SR-2. It was only when I started working with Excel 2000 (which was sent to me free of charge as a fix to a charting bug in Excel 97, SR-2), did I find Excel finally easier to work with than Lotus 1-2-3 v2.3 Lotus 1-2-3 v2.3 is a DOS program, which may be a bit tough to work with in some ways on a system that still uses DOS, but at least it was a relatively stable program. Two of the issues that I had to contend with in Excel 97, SR-2 we When dividing the sum of one range greater than 25 rows by the sum of another range greater than 25 rows, it would return the result as "1/0" When using charts, no matter what setting was used on the legend, the legend would gradually work it's way from the position it was set at to the top center portion of the chart. That was during the days when my VBA knowledge was very limited. Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Ronald Incorrect information on later versions. Excel stores all Menu and Toolbar customizations in the *.XLB file. I believe you are thinking of pre-xl97 when Excel had the old menu editor and stored the customizations in Personal.xls Gord Dibben MS Excel MVP On Thu, 3 May 2007 16:19:12 -0400, "Ronald Dodge" wrote: By default, Excel stores any custom toolbars in the Personal.xls workbook, which is stored under the general startup folder that any user can get to. This workbook is also generally hidden as it's hidden by default. However, the user can attach such toolbars to other workbooks. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding a file in excel
Comments in line -
"Ronald Dodge" wrote in message I have seen that happen in XL97, XL2000, and XL2002, but can't say for XL2003 or later. If you mean these versions store custom toolbars in Personal.xls vs the XLB file, that would only occur if a toolbar was attached to Personal.xls (not typical), otherwise toolbars are indeed stored in the XLB file as Gord says. <snip Two of the issues that I had to contend with in Excel 97, SR-2 we When dividing the sum of one range greater than 25 rows by the sum of another range greater than 25 rows, it would return the result as "1/0" Works fine for me in XL97, IOW I can't recreate that problem, unless of course the result should indeed be 1/0. Perhaps you are referring to certain formulas that reference an array that exceeds the 5461 element limit. The same limitation persisted largley unresolved and poorly documented in XL2000. However I don't relate that to what you describe. When using charts, no matter what setting was used on the legend, the legend would gradually work it's way from the position it was set at to the top center portion of the chart. That was during the days when my VBA knowledge was very limited. I haven't particularly noticed that though XL97 charts are somewhat more 'sensitive' in some unexpected ways. However the SR2 fixed quite a few things with charts. It's history now and despite some bugs, in its day Excel 97 was a killer App ! Regards, Peter T "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Ronald Incorrect information on later versions. Excel stores all Menu and Toolbar customizations in the *.XLB file. I believe you are thinking of pre-xl97 when Excel had the old menu editor and stored the customizations in Personal.xls Gord Dibben MS Excel MVP On Thu, 3 May 2007 16:19:12 -0400, "Ronald Dodge" wrote: By default, Excel stores any custom toolbars in the Personal.xls workbook, which is stored under the general startup folder that any user can get to. This workbook is also generally hidden as it's hidden by default. However, the user can attach such toolbars to other workbooks. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding a file in excel
The formula bug that I mentioned has nothing to do with arrays or range
names, just simply selecting ranges. I don't use arrays all that much on the spreadsheet side, but I do use them on the VBA side. The chart bug was confirmed by MS. MS said it was fixed in XL2000 and that was the bug that had them send XL2000 to me free of charge as a fix to the issue. The issue happened with the combination of the manipulating data, updating charts via calculations, printing the charts, and saving the file that caused the legend to move. The bonus kicker that I didn't expect, XL2000 not only had the various issues fixed that was in XL97, SR-2, but it also ran 3 times faster (Both calculations and macros) than XL97, SR-2. For the toolbar thing, I was just going by what my experience had been. Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "Peter T" <peter_t@discussions wrote in message ... Comments in line - "Ronald Dodge" wrote in message I have seen that happen in XL97, XL2000, and XL2002, but can't say for XL2003 or later. If you mean these versions store custom toolbars in Personal.xls vs the XLB file, that would only occur if a toolbar was attached to Personal.xls (not typical), otherwise toolbars are indeed stored in the XLB file as Gord says. <snip Two of the issues that I had to contend with in Excel 97, SR-2 we When dividing the sum of one range greater than 25 rows by the sum of another range greater than 25 rows, it would return the result as "1/0" Works fine for me in XL97, IOW I can't recreate that problem, unless of course the result should indeed be 1/0. Perhaps you are referring to certain formulas that reference an array that exceeds the 5461 element limit. The same limitation persisted largley unresolved and poorly documented in XL2000. However I don't relate that to what you describe. When using charts, no matter what setting was used on the legend, the legend would gradually work it's way from the position it was set at to the top center portion of the chart. That was during the days when my VBA knowledge was very limited. I haven't particularly noticed that though XL97 charts are somewhat more 'sensitive' in some unexpected ways. However the SR2 fixed quite a few things with charts. It's history now and despite some bugs, in its day Excel 97 was a killer App ! Regards, Peter T "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Ronald Incorrect information on later versions. Excel stores all Menu and Toolbar customizations in the *.XLB file. I believe you are thinking of pre-xl97 when Excel had the old menu editor and stored the customizations in Personal.xls Gord Dibben MS Excel MVP On Thu, 3 May 2007 16:19:12 -0400, "Ronald Dodge" wrote: By default, Excel stores any custom toolbars in the Personal.xls workbook, which is stored under the general startup folder that any user can get to. This workbook is also generally hidden as it's hidden by default. However, the user can attach such toolbars to other workbooks. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding a file in excel
That was good of MS!
I have no doubt the Chart bug, whatever it was, was a problem for you, however generally our experiences with Excel 97 are very different. If anything I find '97 calculates noticeably faster than '2000, and each new version a bit slower than its predecessor. However each newer version is better at managing larger volumes of data. I agree Charts were significantly improved in XL2000 (more stable) but I doubt most users doing typical things would notice the difference. I have done quite a bit of VBA speed testing in successive versions. v5.3/XL97 is perhaps very slightly slower than VBA6 introduced in '2000, though it might depend on what the code does. I don't quite follow the bug you mentioned with ranges in Excel 97 but not to worry. I only commented about it previously to allay potential concerns of any continuing Excel 97 readers. Regards, Peter T "Ronald Dodge" wrote in message ... The formula bug that I mentioned has nothing to do with arrays or range names, just simply selecting ranges. I don't use arrays all that much on the spreadsheet side, but I do use them on the VBA side. The chart bug was confirmed by MS. MS said it was fixed in XL2000 and that was the bug that had them send XL2000 to me free of charge as a fix to the issue. The issue happened with the combination of the manipulating data, updating charts via calculations, printing the charts, and saving the file that caused the legend to move. The bonus kicker that I didn't expect, XL2000 not only had the various issues fixed that was in XL97, SR-2, but it also ran 3 times faster (Both calculations and macros) than XL97, SR-2. For the toolbar thing, I was just going by what my experience had been. Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "Peter T" <peter_t@discussions wrote in message ... Comments in line - "Ronald Dodge" wrote in message I have seen that happen in XL97, XL2000, and XL2002, but can't say for XL2003 or later. If you mean these versions store custom toolbars in Personal.xls vs the XLB file, that would only occur if a toolbar was attached to Personal.xls (not typical), otherwise toolbars are indeed stored in the XLB file as Gord says. <snip Two of the issues that I had to contend with in Excel 97, SR-2 we When dividing the sum of one range greater than 25 rows by the sum of another range greater than 25 rows, it would return the result as "1/0" Works fine for me in XL97, IOW I can't recreate that problem, unless of course the result should indeed be 1/0. Perhaps you are referring to certain formulas that reference an array that exceeds the 5461 element limit. The same limitation persisted largley unresolved and poorly documented in XL2000. However I don't relate that to what you describe. When using charts, no matter what setting was used on the legend, the legend would gradually work it's way from the position it was set at to the top center portion of the chart. That was during the days when my VBA knowledge was very limited. I haven't particularly noticed that though XL97 charts are somewhat more 'sensitive' in some unexpected ways. However the SR2 fixed quite a few things with charts. It's history now and despite some bugs, in its day Excel 97 was a killer App ! Regards, Peter T "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Ronald Incorrect information on later versions. Excel stores all Menu and Toolbar customizations in the *.XLB file. I believe you are thinking of pre-xl97 when Excel had the old menu editor and stored the customizations in Personal.xls Gord Dibben MS Excel MVP On Thu, 3 May 2007 16:19:12 -0400, "Ronald Dodge" wrote: By default, Excel stores any custom toolbars in the Personal.xls workbook, which is stored under the general startup folder that any user can get to. This workbook is also generally hidden as it's hidden by default. However, the user can attach such toolbars to other workbooks. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding a file in excel
All I know, when I used Excel 97, it took the Excel side of the reports 1.5
hours to process the data (at that time, it was over a total of 14 different machine center files, 5 work order based files, and 4 different summary files). The day after I uninstalled Excel 97 and then installed Excel 2000 with nothing else changed, it only took the Excel side of the reports 0.5 hours to process the data. Since that time, some of the calculations has been transferred from formula to VBA code. This was the summary files from the beginning to the end of the reporting process, first each of the different machine files would be opened, process, then closed one by one, then the same for the work order base files. Calculation mode was set to manual cause of the redundant calculations, which it seems to me as though Excel 2000 didn't do nearly as much of the redundant calculations as Excel 97 did. Don't get me wrong, it's still there, but it's not as bad. I don't know what it would have been with Excel 2000, but with Excel 97, with opening the 18 different workbooks when I didn't know anything about VBA other than knowing that it was in Excel, it took Excel 40 to 50 minutes to open and calculation all of those files, but yet, after playing around with VBA as my training at Executrain was delayed by 1.5 months due to the instructor having a medical reason for postponing the 2 day VBA course, I managed to get all 18 of those files opened, processed, and closed in a period of 3 to 5 minutes, but then that was also at a time when it had very small amount of data to process as compared to once we started pulling the data from the DB. That's cause now you dealing with about 100 records per shift per machine rather than just 1 record per shift per machine then. Pulling the data from the DB was in place prior to moving from XL97 to XL2000. I assume this difference had a lot to do with the way compilation took place on the VBA side as I did run into issues with some macros that's been converted to Excel 2000 not working properly on Excel 97. At that time, I was the only person within the building using Excel 2000, so I had to be extremely careful in that regards. On your point about most users, I also agree that most users wouldn't have noticed most of the differences cause there were very few differences on the user side, but there were quite a large number of the differences on the technical side of XL2000 that I noticed. Most users though aren't into the technical side of Excel, so they wouldn't notice those differences. Excel 97 was also crashing a lot on me, but then Excel 2000 didn't crash nearly as much on me. Some of it may have been due to me learning the limitations, but some of it was also cause of the technical differences between the 2 versions. The other factor, I do deal with large volumes of data so that may also had some factor in the speed difference between the 2 versions, which I used VBA to control what get calculated in what order, mainly in sheet order. These files has evolved quite abit over the years and it's going through another transfirmation yet again, though the basic layout has stayed the same and the raw data hasn't changed any. Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "Peter T" <peter_t@discussions wrote in message ... That was good of MS! I have no doubt the Chart bug, whatever it was, was a problem for you, however generally our experiences with Excel 97 are very different. If anything I find '97 calculates noticeably faster than '2000, and each new version a bit slower than its predecessor. However each newer version is better at managing larger volumes of data. I agree Charts were significantly improved in XL2000 (more stable) but I doubt most users doing typical things would notice the difference. I have done quite a bit of VBA speed testing in successive versions. v5.3/XL97 is perhaps very slightly slower than VBA6 introduced in '2000, though it might depend on what the code does. I don't quite follow the bug you mentioned with ranges in Excel 97 but not to worry. I only commented about it previously to allay potential concerns of any continuing Excel 97 readers. Regards, Peter T "Ronald Dodge" wrote in message ... The formula bug that I mentioned has nothing to do with arrays or range names, just simply selecting ranges. I don't use arrays all that much on the spreadsheet side, but I do use them on the VBA side. The chart bug was confirmed by MS. MS said it was fixed in XL2000 and that was the bug that had them send XL2000 to me free of charge as a fix to the issue. The issue happened with the combination of the manipulating data, updating charts via calculations, printing the charts, and saving the file that caused the legend to move. The bonus kicker that I didn't expect, XL2000 not only had the various issues fixed that was in XL97, SR-2, but it also ran 3 times faster (Both calculations and macros) than XL97, SR-2. For the toolbar thing, I was just going by what my experience had been. Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "Peter T" <peter_t@discussions wrote in message ... Comments in line - "Ronald Dodge" wrote in message I have seen that happen in XL97, XL2000, and XL2002, but can't say for XL2003 or later. If you mean these versions store custom toolbars in Personal.xls vs the XLB file, that would only occur if a toolbar was attached to Personal.xls (not typical), otherwise toolbars are indeed stored in the XLB file as Gord says. <snip Two of the issues that I had to contend with in Excel 97, SR-2 we When dividing the sum of one range greater than 25 rows by the sum of another range greater than 25 rows, it would return the result as "1/0" Works fine for me in XL97, IOW I can't recreate that problem, unless of course the result should indeed be 1/0. Perhaps you are referring to certain formulas that reference an array that exceeds the 5461 element limit. The same limitation persisted largley unresolved and poorly documented in XL2000. However I don't relate that to what you describe. When using charts, no matter what setting was used on the legend, the legend would gradually work it's way from the position it was set at to the top center portion of the chart. That was during the days when my VBA knowledge was very limited. I haven't particularly noticed that though XL97 charts are somewhat more 'sensitive' in some unexpected ways. However the SR2 fixed quite a few things with charts. It's history now and despite some bugs, in its day Excel 97 was a killer App ! Regards, Peter T "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Ronald Incorrect information on later versions. Excel stores all Menu and Toolbar customizations in the *.XLB file. I believe you are thinking of pre-xl97 when Excel had the old menu editor and stored the customizations in Personal.xls Gord Dibben MS Excel MVP On Thu, 3 May 2007 16:19:12 -0400, "Ronald Dodge" wrote: By default, Excel stores any custom toolbars in the Personal.xls workbook, which is stored under the general startup folder that any user can get to. This workbook is also generally hidden as it's hidden by default. However, the user can attach such toolbars to other workbooks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding total # of records in an Excel file without scrolling | Excel Discussion (Misc queries) | |||
exported Outlook contacts to an Excel file - finding unwanted ' sy | Excel Discussion (Misc queries) | |||
Need help finding text string in an Excel file | Excel Programming | |||
Finding which version of Excel created a file | Excel Programming | |||
Finding data in another Excel file | Excel Programming |