| 95 | Hi i need to connect to excel from another program i have dde client and i already know how to open sheet, close it, save it and select a range of cells but that's it i'd like to know if there are more commands I could use for example for strarting macro where can I find these commands i looked in msdn and Excel Help file and found nothing HELP regards, Szymon |
| 130 | I cannot get the following code to work (which is in the Excel Help files)--I keep getting the error message "Your formula contains an invalid external reference to a worksheet. Verify that the path, workbook, and range name or cell reference are correct and try again." However, this doesn't make sense since everything else in the Sub Procedure works. I cannot get any code to return the trendline count--I always get various types of error messages. I want to use an IF statement to determine whether a trendline exists so that it can be deleted or added for different chart datasets (series collections). (Count code is almost at end of Sub Procedure) Private Sub DataSetList_Change() Dim strfile As String, intTrendline As Integer, intCount As Integer strfile = Application.ThisWorkbook.Name With Worksheets("Review").ChartObjects(3).Chart .HasTitle = True .ChartTitle.Text = DataSetList Select Case DataSetList Case "Total Authorized-RBM (Cum)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_Q" .SeriesCollection(1).Values = "='" & strfile & "'! Tot_Proj_Author_Cum" .SeriesCollection(2).Values = "='" & strfile & "'! Tot_Proj_Act_Cum" intTrendline = 1 Case "Total Authorized-RBM (Qtr)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_Q" .SeriesCollection(1).Values = "='" & strfile & "'! Tot_Proj_Author_Qtr" .SeriesCollection(2).Values = "='" & strfile & "'! Tot_Proj_Act_Qtr" intTrendline = 0 Case "2002 RBM Budget-Actual (Cum)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_M" .SeriesCollection(1).Values = "='" & strfile & "'! RBM_2002_Budget_Cum" .SeriesCollection(2).Values = "='" & strfile & "'! RBM_2002_Act_Cum" intTrendline=1 Case "2002 RBM Budget-Actual (M)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_M" .SeriesCollection(1).Values = "='" & strfile & "'! RBM_2002_Budget_M" .SeriesCollection(2).Values = "='" & strfile & "'! RBM_2002_Act_M" intTrendline=0 End Select End With MsgBox Worksheets("Review").Charts(3).SeriesCollection (2).Trendlines.Count 'If Worksheets("Review").Charts(3).SeriesCollection(2) '.Trendlines.Count = 1 And intTrendline = 0 Then 'ActiveSheet.ChartObjects("Chart 3").Activate 'ActiveChart.SeriesCollection(2).Trendlines(1).Select 'Selection.Delete 'Else If Worksheets("Review").Charts (3).SeriesCollection(2) '.Trendlines.Count = 0 And intTrendline = 1 Then 'Worksheets("Review").ChartObjects (3).Chart.SeriesCollection(2) '.SeriesCollection(2).Trendlines.Add Type:=xlLinear, Name:="Linear Trend" 'Worksheets("Review").ChartObjects(3).Activate 'End If Worksheets("Review").Range("B5").Activate End Sub |
| 143 | William - You skipped a level in the hierarchy of objects. Change this line: MsgBox Worksheets("Review").Charts(3).SeriesCollection(2).Trendlines.Count to this: MsgBox Worksheets("Review").ChartObjects(3).Chart.SeriesCollection(2) .Trendlines.Count (all one line - watch the email wordwrap). - Jon _______ In article <f36f01c21633$c64fb1d0$b1e62ecf@tkmsftngxa04>, bartusek_william_c@cat.com says... > >I cannot get the following code to work (which is in the >Excel Help files)--I keep getting the error message "Your >formula contains an invalid external reference to a >worksheet. Verify that the path, workbook, and range name >or cell reference are correct and try again." However, >this doesn't make sense since everything else in the Sub >Procedure works. I cannot get any code to return the >trendline count--I always get various types of error >messages. I want to use an IF statement to determine >whether a trendline exists so that it can be deleted or >added for different chart datasets (series collections). >(Count code is almost at end of Sub Procedure) > >Private Sub DataSetList_Change() > >Dim strfile As String, intTrendline As Integer, intCount >As Integer > >strfile = Application.ThisWorkbook.Name > >With Worksheets("Review").ChartObjects(3).Chart > .HasTitle = True > .ChartTitle.Text = DataSetList > >Select Case DataSetList > Case "Total Authorized-RBM (Cum)" > .SeriesCollection(1).XValues = "='" & strfile & "'! >Dates_Q" > .SeriesCollection(1).Values = "='" & strfile & "'! >Tot_Proj_Author_Cum" > .SeriesCollection(2).Values = "='" & strfile & "'! >Tot_Proj_Act_Cum" > intTrendline = 1 > > Case "Total Authorized-RBM (Qtr)" > .SeriesCollection(1).XValues = "='" & strfile & "'! >Dates_Q" > .SeriesCollection(1).Values = "='" & strfile & "'! >Tot_Proj_Author_Qtr" > .SeriesCollection(2).Values = "='" & strfile & "'! >Tot_Proj_Act_Qtr" > intTrendline = 0 > > Case "2002 RBM Budget-Actual (Cum)" > .SeriesCollection(1).XValues = "='" & strfile & "'! >Dates_M" > .SeriesCollection(1).Values = "='" & strfile & "'! >RBM_2002_Budget_Cum" > .SeriesCollection(2).Values = "='" & strfile & "'! >RBM_2002_Act_Cum" > intTrendline=1 > > Case "2002 RBM Budget-Actual (M)" > .SeriesCollection(1).XValues = "='" & strfile & "'! >Dates_M" > .SeriesCollection(1).Values = "='" & strfile & "'! >RBM_2002_Budget_M" > .SeriesCollection(2).Values = "='" & strfile & "'! >RBM_2002_Act_M" > intTrendline=0 > >End Select > >End With > >MsgBox Worksheets("Review").Charts(3).SeriesCollection >(2).Trendlines.Count > > 'If Worksheets("Review").Charts(3).SeriesCollection(2) > '.Trendlines.Count = 1 And intTrendline = 0 Then > 'ActiveSheet.ChartObjects("Chart 3").Activate > 'ActiveChart.SeriesCollection(2).Trendlines(1).Select > 'Selection.Delete > > 'Else If Worksheets("Review").Charts >(3).SeriesCollection(2) > '.Trendlines.Count = 0 And intTrendline = 1 Then > 'Worksheets("Review").ChartObjects >(3).Chart.SeriesCollection(2) > '.SeriesCollection(2).Trendlines.Add >Type:=xlLinear, Name:="Linear Trend" > 'Worksheets("Review").ChartObjects(3).Activate > >'End If > >Worksheets("Review").Range("B5").Activate > >End Sub |
| 500 | Hello, Thanks for your reply. I did look into Excel Help, and found the same advice you gave me, but it was not helpful. I think you misunderstand my question I don't have copies of a shared workbook, I have several data files of identical format but with different data and with different numbers of records. I am not trying to merge changes to a shared workbook, I am trying to merge/insert/join/combine these several files into a single worksheet so that I can do some sums on all the records therein. My apologies for the confusion KK "Brian Roberts" <meorelse@hotmail.com> wrote in message news:e3FLlFYGCHA.2212@tkmsftngp12... > Hi, > From Excel Help: > Merge workbooks > 1.. Make sure the copies of the shared workbook that you want to merge are > all stored in the same folder. To do this, you must give each copy a > different name. > 2.. Open the copy into which you want to merge changes. > 3.. On the Tools menu, click Compare and Merge Workbooks. > 4.. If prompted, save the workbook. > 5.. In the Select Files to Merge into Current Workbook dialog box, click a > copy that has changes to be merged, and then click OK. > To merge more than one copy at the same time, hold down CTRL or SHIFT and > click the file names, and then click OK. > > "KK" <trebor@yeleek.nospam.freeserve.co.uk> wrote in message > news:aev43e$r$1@newsg3.svr.pol.co.uk... > > Hello, > > > > > > I have a number of data files generated by a Vbasic program. The files are > > of identical format though with a varying number of records. I need to > open > > all these data files into a single Excel worksheet and do some simple > sums. > > > > I'm sure I'm missing something simple, but I can't see how to merge > several > > excel files into a single worksheet, other than using copy / paste which > > seems a bit clumsy. > > > > With thanks > > > > KK > > > > > > > > |
| 506 | You could have a macro open each text(?) file individually and then have your macro do the copy and paste. But if you recall your old DOS commands, there was a way to merge text files into one. Let's say they are named MyFile0001.txt through myFile0999.txt. And they're in the same folder. Just shell to DOS (Windows Start button|Programs|MS DOS Prompt (for win98)) May be "command prompt" in NT (IIRC). Then traverse to that folder. when you're there, just type: copy myfile*.txt allfiles.txt All the files that start with myfile and have an extension of .txt will be copied into allfiles.txt. Then Exit from the dos prompt and open that one in excel. ======== There are a few warnings about the DOS copy command. You couldn't do this without getting into trouble. copy *.txt allfiles.txt It would try to add allfiles.txt to allfiles.txt. You could even do something like: copy c:\mydir\*.* c:\allfiles.txt by putting the "to" file into a separate folder, we can combine all the files using a wildcard. ======== If you still want a macro to open up each text file and do the copy and paste, post back with some more details. Are the files all in one folder? Are they the only files in that folder? If not is there a unique identifier for the group? (It's kind of the same answers you need to do the DOS copy solution, too.) good luck, KK wrote: > > Hello, > > Thanks for your reply. I did look into Excel Help, and found the same advice > you gave me, but it was not helpful. I think you misunderstand my question > > I don't have copies of a shared workbook, I have several data files of > identical format but with different data and with different numbers of > records. > > I am not trying to merge changes to a shared workbook, I am trying to > merge/insert/join/combine these several files into a single worksheet so > that I can do some sums on all the records therein. > > My apologies for the confusion > > KK > > "Brian Roberts" <meorelse@hotmail.com> wrote in message > news:e3FLlFYGCHA.2212@tkmsftngp12... > > Hi, > > From Excel Help: > > Merge workbooks > > 1.. Make sure the copies of the shared workbook that you want to merge > are > > all stored in the same folder. To do this, you must give each copy a > > different name. > > 2.. Open the copy into which you want to merge changes. > > 3.. On the Tools menu, click Compare and Merge Workbooks. > > 4.. If prompted, save the workbook. > > 5.. In the Select Files to Merge into Current Workbook dialog box, click > a > > copy that has changes to be merged, and then click OK. > > To merge more than one copy at the same time, hold down CTRL or SHIFT > and > > click the file names, and then click OK. > > > > "KK" <trebor@yeleek.nospam.freeserve.co.uk> wrote in message > > news:aev43e$r$1@newsg3.svr.pol.co.uk... > > > Hello, > > > > > > > > > I have a number of data files generated by a Vbasic program. The files > are > > > of identical format though with a varying number of records. I need to > > open > > > all these data files into a single Excel worksheet and do some simple > > sums. > > > > > > I'm sure I'm missing something simple, but I can't see how to merge > > several > > > excel files into a single worksheet, other than using copy / paste which > > > seems a bit clumsy. > > > > > > With thanks > > > > > > KK > > > > > > > > > > > > > -- Dave Peterson ec35720@msn.com |
| 614 | As Peo says, hide it. Once you've done this and close Excel, it'll ask if you want to save your changes to Personal.xls. Choosing 'Yes' will mean that the next time you open Excel, Personal.xls will be hidden from the outset. It doesn't say anything about Personal.xls in Excel Help, but I *believe* that the *only* use of Personal.xls (which is stored in your 'alternate startup file location' - via Tools | Options | General) is for storing macros that you want to have available for all spreadsheets. (Please can somebody correct me if this is wrong.) I know that it ain't the default template (cf. Normal.dot in Word) because that's Book.xlt, if you have it, which sits in your XLStart folder. "Supereal" <none@none.com> wrote in message news:11e6901c21b91$9e6ebae0$3aef2ecf@TKMSFTNGXA09... > Whenever I start up excel, rather than a new workbook, > PERSONAL.XLS starts up in its place. Also, whenever I > double click another excel file to open up, excel starts, > open my file and also opens up PERSONAL.XLS. Does anyone > know why this is happening and how to stop it? Thanks. > > SnR |
| 620 | The Personal.xls problem seems to be well covered. The second instance of Excel opening upon double-click is usually resolved by Tools>Options>General> uncheck "Ignore other Applications". HTH Gord Dibben Excel MVP - XL97 SR2 On Mon, 24 Jun 2002 18:35:00 +0100, "ikr" <ripsher[nospam]@btinternet.com> wrote: >As Peo says, hide it. Once you've done this and close Excel, it'll ask if >you want to save your changes to Personal.xls. Choosing 'Yes' will mean that >the next time you open Excel, Personal.xls will be hidden from the outset. >It doesn't say anything about Personal.xls in Excel Help, but I *believe* >that the *only* use of Personal.xls (which is stored in your 'alternate >startup file location' - via Tools | Options | General) is for storing >macros that you want to have available for all spreadsheets. (Please can >somebody correct me if this is wrong.) I know that it ain't the default >template (cf. Normal.dot in Word) because that's Book.xlt, if you have it, >which sits in your XLStart folder. > >"Supereal" <none@none.com> wrote in message >news:11e6901c21b91$9e6ebae0$3aef2ecf@TKMSFTNGXA09... >> Whenever I start up excel, rather than a new workbook, >> PERSONAL.XLS starts up in its place. Also, whenever I >> double click another excel file to open up, excel starts, >> open my file and also opens up PERSONAL.XLS. Does anyone >> know why this is happening and how to stop it? Thanks. >> >> SnR > |
| 654 | I keep my personal.xl* in my XLStart folder (along with book.xlt and sheet.xlt). I don't think I've ever pointed to any folder for the "alternate startup file location" (renamed in xl2002 to: "at startup, open all files in:"). ikr wrote: > > As Peo says, hide it. Once you've done this and close Excel, it'll ask if > you want to save your changes to Personal.xls. Choosing 'Yes' will mean that > the next time you open Excel, Personal.xls will be hidden from the outset. > It doesn't say anything about Personal.xls in Excel Help, but I *believe* > that the *only* use of Personal.xls (which is stored in your 'alternate > startup file location' - via Tools | Options | General) is for storing > macros that you want to have available for all spreadsheets. (Please can > somebody correct me if this is wrong.) I know that it ain't the default > template (cf. Normal.dot in Word) because that's Book.xlt, if you have it, > which sits in your XLStart folder. > > "Supereal" <none@none.com> wrote in message > news:11e6901c21b91$9e6ebae0$3aef2ecf@TKMSFTNGXA09... > > Whenever I start up excel, rather than a new workbook, > > PERSONAL.XLS starts up in its place. Also, whenever I > > double click another excel file to open up, excel starts, > > open my file and also opens up PERSONAL.XLS. Does anyone > > know why this is happening and how to stop it? Thanks. > > > > SnR -- Dave Peterson ec35720@msn.com |
| 829 | Hi, You don't really need a macro for this. The VLOOKUP commmand should do the job nicely. Just search for vlookup in Excel Help for all the info you need. It would be easier if your sheet containing part numbers and descriptions was a different sheet in the same workbook, rather than in another workbook. HTH, Steve >-----Original Message----- > >Hello, >I have a form template (write-up rev 031902) and need help with the >following: > >In my form I have B14......B29 ...in these cells I put in a part number... >and would like the description for that part number to be added into >C14....C29 > >For the description and part number I have created another workbook >named "partdesc"......were column A..has the part numbers and they start >from Cell 3 down and column B has the description for those part numbers. > >How can I write a macro for this... > >Thanks >-- >JR > > >. > |
| 842 | Thank you Steven for the information... They also mentioned Lookup......but I can't seem to be able to add that function in. It's an add-in. Thanks.... JR "Steven McDonald" <a@a.com> wrote in message news:12a8c01c21d27$b9729720$3aef2ecf@TKMSFTNGXA09... > > Hi, > > You don't really need a macro for this. The VLOOKUP > commmand should do the job nicely. Just search for vlookup > in Excel Help for all the info you need. > > It would be easier if your sheet containing part numbers > and descriptions was a different sheet in the same > workbook, rather than in another workbook. > > HTH, > > Steve > > >-----Original Message----- > > > >Hello, > >I have a form template (write-up rev 031902) and need > help with the > >following: > > > >In my form I have B14......B29 ...in these cells I put > in a part number... > >and would like the description for that part number to be > added into > >C14....C29 > > > >For the description and part number I have created > another workbook > >named "partdesc"......were column A..has the part numbers > and they start > >from Cell 3 down and column B has the description for > those part numbers. > > > >How can I write a macro for this... > > > >Thanks > >-- > >JR > > > > > >. > > |
| 1271 | HOw do u caclulate MIRR if there are more than one negative cashflows... Please dont ask me to use excel..or see the Excel Help for the formula. the formula does not give the right answer.. |
| 1341 | Whilst this looks as if it would provide the solution, unfortunately, I work for the National Health Service and I'll find it difficult to persuade my manager to pay for it! Thanks anyway. Any other suggestions from elswhere????? >-----Original Message----- >Airlie, take a look at the Data Loader on my site. > >Bob Flanagan >Macro Systems >http://www.add-ins.com >Productivity add-ins and downloadable books on VB macros for Excel > >"B Airlie" <bernard.airlie@laht.scot.nhs.uk> wrote in message >news:1231301c221e0$8b661830$a5e62ecf@tkmsftngxa07... >> I am desperate! All I am trying to do is merge two >> different spreadsheets, where the common field is Order >> Number. >> >> Where the order nubmer exists on both sheets, I would like >> to transfer the Cost Centre from the 2nd sheet onto the >> first sheet and show it against the line on that sheet >> where the order number matches. Hopefully, the result >> would be as shown on the green area of the attached file. >> >> I've used the Excel Help facility and dabbled with Lookup, >> but with (very!) limited success. Any help would be >> greatly appreciated. >> >> Thanks. > > >. > |
| 1507 | Hi Stuart I hope I didn't give you the same impression that Tom has got from my response. I was only suggesting that you see if the "Iterations" checkbox was un-checked now, where it might have been checked before. That it why i copied in the Excel Help about the Checkbox. -- Kind Regards Dave Hawley www.MicrosoftExcelTraining.com \ www.OzGrid.com Get 8 Add-ins in one! For less than the price of 1 http://www.ozgrid.com/Services/AddinExamples.htm FREE EXCEL NEWSLETTER http://www.ozgrid.com/News/2home.htm "Tom Ogilvy" <twogilvy@msn.com> wrote in message news:OQWTmq3ICHA.1772@tkmsftngp09... | If you checked iteration, it tells Excel to allow circular references - | doesn't mean it is fixed. | | Regards, | Tom Ogilvy | | Stuart <stuart.green@Adelphi.co.uk> wrote in message | news:12c2701c22357$f3252560$9ee62ecf@tkmsftngxa05... | > Thanks very much for your reply - I hadn't spotted this, | > and had tried looking on the support site but nothing | > seemed to quite match what was happening. | > | > It seems to be working OK now - I guess I just need to get | > rid of a few (a lot?) of formulae! | > | > Thanks again, | > Stuart | > | > | > >-----Original Message----- | > >Hi Stuart | > > | > >If you have not changed any formulae references, try | > going to | > >Tools>Option>Calculation and see whether "Iterations" is | > checked. From Excel | > >Help: | > > | > >Limits iteration for goal seeking or for resolving | > circular references. | > >Unless you specify otherwise, Microsoft Excel stops after | > 100 iterations or | > >when all values change by less than 0.001. You can limit | > iteration by | > >changing the number in the Maximum iterations box, the | > Maximum change box, | > >or both boxes. | > > | > >If you toggle this and have your Status bar visible, you | > should see the | > >address of the Circular in there when you select a Sheet | > that has one. | > > | > > | > >-- | > >Kind Regards | > >Dave Hawley | > > www.MicrosoftExcelTraining.com \ www.OzGrid.com | > > Get 8 Add-ins in one! For less than the price of 1 | > > http://www.ozgrid.com/Services/AddinExamples.htm | > > FREE EXCEL NEWSLETTER | > > http://www.ozgrid.com/News/2home.htm | > > | > > | > > | > >"Stuart" <stuart.green@adelphi.co.uk> wrote in message | > >news:12df501c2234b$fd01fb00$a5e62ecf@tkmsftngxa07... | > >| I have a file that seems to be working fine. When I save | > >| it as a new version it suddenly finds lots of circular | > >| references. I have not changed anything and this will be | > >| the 9th version of the same file - no previous versions | > >| have had this problem. Does anyone have any ideas why? | > >| | > >| many thanks in advance for your help! | > >| Stuart | > > | > > | > >. | > > | | |
| 1533 | Thanks Dave ... that is just the ticket. I have about 4 workbooks that have to be open ... I presume that I can call them "otherWkbk1" & "otherWkbkName1" , and so on? Follow up question ... Where do I put this to get it to run automatically when the template is open ? Can't find any reference to 'auto run', other than how to disable it. ( Is it just me, or is Excel Help, not ? ) "Dave Peterson" <ec35720@msn.com> wrote in message news:3D24467B.D1B3B382@msn.com... > Here's one way: > > Option Explicit > > Sub testme() > > Dim otherWkbk As Workbook > Dim otherWkbkName As String > > otherWkbkName = "book2.xls" > > Set otherWkbk = Nothing > On Error Resume Next > Set otherWkbk = Workbooks(otherWkbkName) > On Error GoTo 0 > > If otherWkbk Is Nothing Then > 'not found, so open > Set otherWkbk = Workbooks.Open _ > (Filename:="C:\my documents\excel\" & otherWkbkName) > Else > 'already open--don't do anything > End If > > End Sub > > > > Brian Belliveau wrote: > > > > Can anyone guide me down the route of having a template, on being opened, > > check to see if another file (on which it depends for data) is open, and if > > not, open that file. > > > > Exel XP > > > > Thanks to all > > -- > > Dave Peterson > ec35720@msn.com |
| 1569 | The easy part first. If you name your macro Auto_open, you'll get what you want. Alternatively, you could put it in the module behind ThisWorkbook and call it Workbook_Open. (the auto_open is supported to be compatible with older versons of excel. The "newer" way (xl97 and greater) is to the workbook_open event. Either should be ok for this purpose. --- The harder question. Yep, you could make otherwkbkname1,...otherwkbkname4. But that gets kind of boring after about 3 (and near impossible when you hit double digits!). One way to avoid having to do it is to use an array to hold the names. This might work for you: Option Explicit Sub auto_open() Dim otherWkbk() As Workbook Dim otherWkbkNames As Variant Dim otherFolderName As String Dim iCtr As Long otherWkbkNames = Array("book1.xls", "book2.xls", "book3.xls", "book4.xls ") ReDim otherWkbk(LBound(otherWkbkNames) To UBound(otherWkbkNames)) otherFolderName = "C:\my documents\excel\" If Right(otherFolderName, 1) <> "\" Then otherFolderName = otherFolderName & "\" End If For iCtr = LBound(otherWkbkNames) To UBound(otherWkbkNames) Set otherWkbk(iCtr) = Nothing On Error Resume Next Set otherWkbk(iCtr) = Workbooks(otherWkbkNames(iCtr)) On Error GoTo 0 If otherWkbk(iCtr) Is Nothing Then 'not found, so open Set otherWkbk(iCtr) = Workbooks.Open _ (Filename:=otherFolderName & otherWkbkNames(iCtr)) Else Set otherWkbk(iCtr) = Workbooks(otherWkbkNames(iCtr)) End If Next iCtr End Sub You define an array to hold all your names and cycle through them one at a time. Notice that I added a variable to hold the folder. (I did assume that the other 4 workbooks come from the same folder.) Also, by using this Set otherwkbk(ictr) = ... stuff (either the already opened or the one that is being opened), I can use this: otherwkbk(1).worksheets("sheet1").range("a1:a5").copy .... instead of refering to the workbook by name. workbooks("book1.xls").worksheets("sheet1").range("a1:a5").copy .... If you use this kind of thing, then if book1.xls changes (from testing mode to production or just because things change), you'll have fewer spots to update (and therefore, fewer spots to miss updating!). Brian Belliveau wrote: > > Thanks Dave ... that is just the ticket. > > I have about 4 workbooks that have to be open ... I presume that I can call > them "otherWkbk1" & "otherWkbkName1" , and so on? > > Follow up question ... > > Where do I put this to get it to run automatically when the template is open > ? Can't find any reference to 'auto run', other than how to disable it. ( Is > it just me, or is Excel Help, not ? ) > > "Dave Peterson" <ec35720@msn.com> wrote in message > news:3D24467B.D1B3B382@msn.com... > > Here's one way: > > > > Option Explicit > > > > Sub testme() > > > > Dim otherWkbk As Workbook > > Dim otherWkbkName As String > > > > otherWkbkName = "book2.xls" > > > > Set otherWkbk = Nothing > > On Error Resume Next > > Set otherWkbk = Workbooks(otherWkbkName) > > On Error GoTo 0 > > > > If otherWkbk Is Nothing Then > > 'not found, so open > > Set otherWkbk = Workbooks.Open _ > > (Filename:="C:\my documents\excel\" & otherWkbkName) > > Else > > 'already open--don't do anything > > End If > > > > End Sub > > > > > > > > Brian Belliveau wrote: > > > > > > Can anyone guide me down the route of having a template, on being > opened, > > > check to see if another file (on which it depends for data) is open, and > if > > > not, open that file. > > > > > > Exel XP > > > > > > Thanks to all > > > > -- > > > > Dave Peterson > > ec35720@msn.com -- Dave Peterson ec35720@msn.com |
| 2524 | Information from Excel Help files: CONCATENATE See Also Joins several text strings into one text string. Syntax CONCATENATE (text1,text2,...) Text1, text2, ... are 1 to 30 text items to be joined into a single text item. The text items can be text strings, numbers, or single-cell references. Remarks The "&" operator can be used instead of CONCATENATE to join text items. Examples CONCATENATE("Total ", "Value") equals "Total Value". This is equivalent to typing "Total"&" "&"Value" Suppose in a stream survey worksheet, C2 contains "species", C5 contains " brook trout", and C8 contains the total 32. CONCATENATE("Stream population for ",C5," ",C2," is ",C8,"/mile") equals "Stream population for brook trout species is 32/mile" Regards Trev "Dennis Nickel" <dnickel@burkeandnickel.com> wrote in message news:1709e01c22d12$82f99090$36ef2ecf@tkmsftngxa12... > I have two columns of numbers which are parts of an asset > number (ex 1234 in one cell and 000 in the cell in the > next column). I need to combine the two cells in the > different columns together into one cell. Can anyone tell > me the steps needed to combine the two columns together to > accomplish this task? |
| 2700 | When I go to the Help | Microsoft Excel Help menu, the Index and Answer Wizard tabs are blank thererfore are unusable. The Contents tab is fine. I tried re-installing Help this morning but it did not change this situation. Another clue to the problem may be that MS Word has the same problem. I am running Windows 2000, Office XP Small Business. Thanks for your help. Rick |
| 3032 | I know the Doc says this is supposed to happen but I'm trying to find a work around where I can delete rows without getting the #ref error. I'm basically creating a schedule and I have a Num Days, Start Date and End Date Column. My Start Date formula is =The Last end date. For Example A1 5 B1 8/1/2002 C1 =WORKDAY(B1,A1) A2 3 B2 =C1 C2=WORKDAY(B2,A2) A3 1 B3=C2 C3=WORKDAY(B3,A3) If I delete Row 2 I get a Bad Ref in B3 - which is what Excel Help says but is there a way I can get it to change to B2=C1 like I would want when deleting Row 2. Thanks for any help in advance. |
| 3036 | In B2 use =OFFSET(B2,-1,1) and copy down -- Regards, Peo Sjoblom "Keith Patella" <keith@stainlesssteelstudios.com> wrote in message news:OKT1QnxMCHA.2480@tkmsftngp12... > I know the Doc says this is supposed to happen but I'm trying to find a > work around where I can delete rows without getting the #ref error. > > I'm basically creating a schedule and I have a Num Days, Start Date and End > Date Column. My Start Date formula is =The Last end date. For Example > > A1 5 B1 8/1/2002 C1 =WORKDAY(B1,A1) > A2 3 B2 =C1 C2=WORKDAY(B2,A2) > A3 1 B3=C2 C3=WORKDAY(B3,A3) > > If I delete Row 2 I get a Bad Ref in B3 - which is what Excel Help says but > is there a way I can get it to change to B2=C1 like I would want when > deleting Row 2. > > Thanks for any help in advance. > > |
| 3075 | Worked Perfect, Thanks "Peo Sjoblom" <terre08@mvps.org> wrote in message news:#IcRaxxMCHA.1620@tkmsftngp10... > In B2 use > > =OFFSET(B2,-1,1) > > and copy down > > -- > > Regards, > > Peo Sjoblom > > > "Keith Patella" <keith@stainlesssteelstudios.com> wrote in message > news:OKT1QnxMCHA.2480@tkmsftngp12... > > I know the Doc says this is supposed to happen but I'm trying to find > a > > work around where I can delete rows without getting the #ref error. > > > > I'm basically creating a schedule and I have a Num Days, Start Date and > End > > Date Column. My Start Date formula is =The Last end date. For Example > > > > A1 5 B1 8/1/2002 C1 =WORKDAY(B1,A1) > > A2 3 B2 =C1 C2=WORKDAY(B2,A2) > > A3 1 B3=C2 C3=WORKDAY(B3,A3) > > > > If I delete Row 2 I get a Bad Ref in B3 - which is what Excel Help says > but > > is there a way I can get it to change to B2=C1 like I would want when > > deleting Row 2. > > > > Thanks for any help in advance. > > > > > > |
| 3079 | My Pleasure.. -- Regards, Peo Sjoblom "Keith Patella" <keith@stainlesssteelstudios.com> wrote in message news:Oq3Qb0yMCHA.2468@tkmsftngp12... > Worked Perfect, Thanks > > "Peo Sjoblom" <terre08@mvps.org> wrote in message > news:#IcRaxxMCHA.1620@tkmsftngp10... > > In B2 use > > > > =OFFSET(B2,-1,1) > > > > and copy down > > > > -- > > > > Regards, > > > > Peo Sjoblom > > > > > > "Keith Patella" <keith@stainlesssteelstudios.com> wrote in message > > news:OKT1QnxMCHA.2480@tkmsftngp12... > > > I know the Doc says this is supposed to happen but I'm trying to > find > > a > > > work around where I can delete rows without getting the #ref error. > > > > > > I'm basically creating a schedule and I have a Num Days, Start Date and > > End > > > Date Column. My Start Date formula is =The Last end date. For Example > > > > > > A1 5 B1 8/1/2002 C1 =WORKDAY(B1,A1) > > > A2 3 B2 =C1 C2=WORKDAY(B2,A2) > > > A3 1 B3=C2 C3=WORKDAY(B3,A3) > > > > > > If I delete Row 2 I get a Bad Ref in B3 - which is what Excel Help says > > but > > > is there a way I can get it to change to B2=C1 like I would want when > > > deleting Row 2. > > > > > > Thanks for any help in advance. > > > > > > > > > > > > |
| 3316 | We are a graphic design and printing company that keeps our Master Schedule on an Excel spreadsheet file. It has 53 fields and has become quite unwieldy for data entry and reference. I want to create forms for this purpose, probably several different forms, each one displaying only certain fields depending on the purpose. I checked the Excel Help and found four alternatives: 1. Use a built-in Microsoft Excel data form to work with a Microsoft Excel list 2. Use the Template Wizard to create a data entry form for a Microsoft Excel list 3. Create a Microsoft Access form to work with a Microsoft Excel list 4. Use an existing Microsoft Access form to enter data in a Microsoft Excel list I think the choice will be either 2 or 3, because I cannot imagine there is already a form that would suit our purpose. An initial look at the Template Wizard left me with more questions than answers. I have successfully created forms in Access, so that appeals to me more, though I don't know yet how to import a form into Excel. We have Excel on all our machines, but have only two or three licenses for Access. If I used an Access form, could it be standalone or runtime version of Access, or would I have to have an Access license for every station? Why not just convert it to an Access database? Other than the expense of those licenses, there is also the problem that we make certain calculations in the spreadsheet, such as adding up the dollar value of orders recorded in a range of dates. Instead of wasting a lot of time trying to decide which route to pursue, I would appreciate some guidance that would point me in the best direction. I have a big book called Microsoft XP Inside Out. The Excel section doesn't seem to cover what I need to know on this topic. |
| 3770 | Nice ideas... "Dana DeLouis" <ng_only@hotmail.com> wrote in message news:#REEsJ#NCHA.2708@tkmsftngp13... > If you are interested, you might be able to write a macro to sort Blanks to > the Top. > Here is a quick conversion of something that I use. I removed lines from > the code that work with formulas. > You can play with it to get it to do what you want. > This assumes the data is Text (No Formulas), and that you have a Heading Row > at the Top of your Sort range. > This Sorts A-Z, with Blanks on Top. > The reason for "Areas.Count" is that if you do have Blanks, then you Will > have a heading row (First Area), Blanks, then your data (Second Area) > The Area here will not have a Heading Row. If there are no blanks, then > there will be 1 area, "With" a heading Row. > Again, you can be as creative as you want here. This is just a general idea > to play with. > > Another idea in addition to David's for Undoing a VBA Sort is to have VBA > turn on Track Changes prior to VBA sorting your data. It works pretty well > in Excel XP. > Some do not like this idea because the Workbook becomes "Shared" while > tracking changes. For home use, I think it's a pretty neat way to "Undo" > whatever VBA might have done to your workbook. > > Sub SortAZBlanksOnTop() > ' Dana DeLouis > ' Sort Ascending, Blanks on Top > > On Error Resume Next > With Selection > .SpecialCells(xlCellTypeBlanks) = "#N/A" > .Sort _ > Key1:=.Cells(1), _ > Order1:=xlDescending, _ > Header:=xlYes > .SpecialCells(xlCellTypeConstants, 16).ClearContents > With .SpecialCells(xlCellTypeConstants, 7) > If .Areas.Count = 1 Then > .Sort _ > Key1:=.Cells(1), _ > Order1:=xlAscending, _ > Header:=xlYes > Else > .Areas(.Areas.Count).Sort _ > Key1:=.Cells(1), _ > Order1:=xlAscending, _ > Header:=xlNo > End If > End With > End With > End Sub > > -- > Dana DeLouis > Windows XP & Office XP > = = = = = = = = = = = = = = = = = > > > "Tim Zych" <tzych@earthlink.net> wrote in message > news:OXOslq9NCHA.2224@tkmsftngp09... > > Hi Dave: > > > > I agree that it's useful for Excel. I am wondering if there is a reason > that > > goes to the core of the way Excel is designed, or rather is this the "Gee, > > Microsoft's tests confirm that users prefer this" sort of reason. > > > > Btw, StarCalc does the same thing. > > > > Why doesn't Access offer the same "convenience"? Or Word? > > > > Just curious. > > > > "David McRitchie" <dmcritchie@msn.com> wrote in message > > news:eoZaEu8NCHA.2488@tkmsftngp09... > > > Hi Tim, > > > Empty Cells, and cells with blanks sorting below > > > other data sure is helpful compared to having them > > > bunched up at the front. The following information > > > is from or draws from my page: > > > Sorting, Some notes on sorting in Excel > > > http://www.mvps.org/dmcritchie/excel/sorting.htm > > > > > > Excel modifies the ASCII sorting sequence anyway. > > > Numbers are sorted from the smallest negative number > > > to the largest positive number, followed by Text, and text > > > that includes numbers. > > > Text is sorted in this order, with lowercase being > > > equivalent to uppercase. > > > Actually Microsoft Excel Help indicates the following order, > > > which is not the ASCII collating sequence, doesn't include > > > all of the 128 characters, and is not exactly match my > > > experience for sorting text (try for singles and multiples): > > > 0 1 2 3 4 5 6 7 8 9 ' - (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ > _ > > ` > > > { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z > > > In logical values, FALSE is sorted before TRUE. > > > All error values are equal. > > > Blanks are always sorted last. -OR- so it says . > > > > > > HTH, > > > David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] > > > My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm > > > Search Page: http://www.mvps.org/dmcritchie/excel/search.htm > > > > > > "Tim Zych" <tzych@earthlink.net> wrote in message > > news:Of3xA03NCHA.1624@tkmsftngp10... > > > > Why are blanks in Excel placed last in a sort, while in Access and > Word > > they > > > > are placed first? > > > > > > > > Thanks. > > > > Tim > > > > > > > > > > > > > > > > > > > > |
| 3801 | Use <= for less than or equal to For a list of operators, search for 'calculation operators' in Excel Help gregork wrote: > How do you write up a less than or equal to formula (there is no sign on the > key for this on my keyboard). For example =IF A1( <or =To A2) ?????How do > you write this???? > > GregK > > -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
| 3932 | Excel 97, I have an existing chart with months along the bottom and £value up the left side. I now want to add a secondary axis on the right hand side (no. of) Excel Help says to click the series then Format menu - Data series click axis tab then click secondary axis. My problem is when I click on Format menu.. there is no Data Series to click on !! Any suggestions how I create a secondary axis. Bob |
| 3933 | When you click on one of the columns or lines (don't know what kind of chart you've created), you've selected a data series. Then choose Format, and the first item in the menu should be 'Selected Data Series.' If it says something else, then something else in the chart is currently selected. You can also select objects in the chart by choosing from the dropdown list on the Chart menu. Robert Gillard wrote: > Excel 97, > > I have an existing chart with months along the bottom and £value up the left > side. I now want to add a secondary axis on the right hand side (no. of) > > Excel Help says to click the series then Format menu - Data series click > axis tab then click secondary axis. > > > My problem is when I click on Format menu.. there is no Data Series to click > on !! > > Any suggestions how I create a secondary axis. > > Bob > > -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
| 3980 | When I installed Excel 97, I chose to install visual basic help. I seem to recall that there was a way to separate the visual basic section of the help from the main help section for Excel. I can't remember it, and I don't want to get in trouble by experimenting with moving help files around. I don't want to completely uninstall visual basic help: I just want to be able to search Excel Help without what I am looking for being buried in help topics for a long list of VB commands and functions. If I want visual basic help, I can always get it from the VB editor or a simple shortcut to the VB help file. (It is GREAT to be back to usenet, I guess sometimes the good old days CAN be recovered.) Thanks! |
| 3982 | You need to have at least 2 data series in order to implement a 2nd Y axis. Is that the case? On Sat, 03 Aug 2002 12:14:42 GMT, "Robert Gillard" <bob@mystical.demon.co.uk> wrote: >Excel 97, > >I have an existing chart with months along the bottom and £value up the left >side. I now want to add a secondary axis on the right hand side (no. of) > >Excel Help says to click the series then Format menu - Data series click >axis tab then click secondary axis. > > >My problem is when I click on Format menu.. there is no Data Series to click >on !! > >Any suggestions how I create a secondary axis. > >Bob > |
| 4374 | I recently got a new computer and switched from Windows NT/Office 2000 to Windows XP/Office 2002. I transfered all my excel files through our network to my new computer. But now my excel files that have formulas in them take forever to do anything with. Whenever I type in a new value it wants to recalculate everything. The calculation setting is on Automatic, like it was on my old pc...so I don't understand why this should take longer. It seems to do this only on files that are bigger, and have formulas that reference a lot of cells...but I'm not totally sure about that. I have looked all over the web, and on the Excel Help and have tried to switch to Manual calculation....but when I press F9 it takes just as long to do the calculation......HELP!!!!! |
| 4531 | Michael If you go to Excel Help and type in "shortcut"(no quotes) you will find a listing of all the shortcut key combos, including the ones Andy gave you. HTH Gord Dibben Excel MVP - XL97 SR2 On Sat, 10 Aug 2002 12:40:42 -0700, "Michael" <michael@mhitg.com> wrote: >Andy, > >You would not believe how long I have been wondering how >to do that. Thank you!! > >Regards, >Michael |
| 4581 | This question applies to Excel 97 & 2k. In a template or other worksheet: how do I cause the active cell to move automatically to a designated cell that is not adjacent to the current active cell? Example: I make an entry in A1. After I press enter, I want the active cell to be A4 without having to press enter 2 more times. Next, I make an entry in A4. After I press enter, I want the active cell to be B1. Is this issue addressed in MS Press "Running MS Excel 2000" or Excel Help? |
| 4582 | right click on sheet tab>view code> insert this Private Sub Worksheet_Change(ByVal Target As Range) 'If Target.Column = 1 Then Target.Offset(3).Select If Target.Row = 1 Then Target.Offset(3).Select If Target.Row = 4 Then Target.Offset(-3, 1).Select End Sub -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "don" <donwatt@comcast.net> wrote in message news:0ab701c2417c$1f1e1310$b1e62ecf@tkmsftngxa04... > This question applies to Excel 97 & 2k. > > In a template or other worksheet: how do I cause the > active cell to move automatically to a designated cell > that is not adjacent to the current active cell? Example: > > I make an entry in A1. After I press enter, I want the > active cell to be A4 without having to press enter 2 more > times. Next, I make an entry in A4. After I press enter, > I want the active cell to be B1. > > Is this issue addressed in MS Press "Running MS Excel > 2000" or Excel Help? |
| 4586 | Don Other Don gave you a programming method. I will suggest a non-programming method. Select the Second cell you want in the range(A4) then CRTL + click your way through the range, ending with the First cell(A1). Name this range under Insert>Name>Define>OK. Now click on NameBox, select the named range and you can Tab or Enter your way through the range in the order you selected starting at A1. There is a limit of about 25-40 cells you can put into a Named Range due to a 255 character limit in the Named range. HTH Gord Dibben Excel MVP - XL97 SR2 On Sun, 11 Aug 2002 14:14:47 -0700, "don" <donwatt@comcast.net> wrote: >This question applies to Excel 97 & 2k. > >In a template or other worksheet: how do I cause the >active cell to move automatically to a designated cell >that is not adjacent to the current active cell? Example: > >I make an entry in A1. After I press enter, I want the >active cell to be A4 without having to press enter 2 more >times. Next, I make an entry in A4. After I press enter, >I want the active cell to be B1. > >Is this issue addressed in MS Press "Running MS Excel >2000" or Excel Help? |
| 4972 | I didn't open your workbook, but I think Excel's Data * Table command will do what you want. Details are in the Excel Help file. John Walkenbach For Excel tips, macros, & downloads... http://j-walk.com/ss "Martin Stötzel" <adm_02@aesculap.com> wrote in message news:2b1b01c2446a$cfe74ea0$b1e62ecf@tkmsftngxa04... > Hello, > > I have an Excel table where I calculate my annual Profit, > using the simple formula Income-Expenses. > Income and Expenses depend on many variables, e.g. number > of employees. > I now want to draw a chart which shows the Profit and its > dependence on one certain variable. > For example, what is profit forecast when I have 10, 20, > 30, 40, 50 employees and the rest of the variables remain > constant. > > My problem is that the calculation is rather complicated > and I don't want to calculate 10 times different Income, > different Expense, and new Profit. > > Is there a possibility to draw a chart of the profit based > on only one number (employees: one single field or cell), > allowing me to specify a certain range for the number of > employees (10 - 100 in 10 steps)? > > Thank you for your help, > > Martin |
| 4976 | A better description of Data Tables, with examples, can be found at this link: http://faculty.fuqua.duke.edu/~pecklund/excelreview/2001_Documents/XLDataTable sMonochrome2001.pdf - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ In article <OZ3ZXdIRCHA.4228@tkmsftngp08>, john@j-walk.com says... > >I didn't open your workbook, but I think Excel's Data * Table command will >do what you want. Details are in the Excel Help file. > >John Walkenbach >For Excel tips, macros, & downloads... >http://j-walk.com/ss > > >"Martin Stötzel" <adm_02@aesculap.com> wrote in message >news:2b1b01c2446a$cfe74ea0$b1e62ecf@tkmsftngxa04... >> Hello, >> >> I have an Excel table where I calculate my annual Profit, >> using the simple formula Income-Expenses. >> Income and Expenses depend on many variables, e.g. number >> of employees. >> I now want to draw a chart which shows the Profit and its >> dependence on one certain variable. >> For example, what is profit forecast when I have 10, 20, >> 30, 40, 50 employees and the rest of the variables remain >> constant. >> >> My problem is that the calculation is rather complicated >> and I don't want to calculate 10 times different Income, >> different Expense, and new Profit. >> >> Is there a possibility to draw a chart of the profit based >> on only one number (employees: one single field or cell), >> allowing me to specify a certain range for the number of >> employees (10 - 100 in 10 steps)? >> >> Thank you for your help, >> >> Martin > > |
| 5494 | Not exactly a combobox, but you can use Data Validation to create a dropdown list in a cell. This is information in Excel Help, and some instructions on my web site: http://www.contextures.com/xlDataVal01.html ggofman wrote: > I know that there is a way to make a Combo dropdown box > with out VBA, right in the sheet. > > can some one please shed some light... > > what I need to do is have a column filled with embeded > combo boxes in the cells themselves. > > Thanks in advance. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
| 6217 | Hi ......, Alt+Shift+F1 It is practically impossible to find a shortcut for something specific using the Excel Help so here is a list of shortcuts that you can use your browser's FIND to help you. Shortcut Keys in Excel 2000 (Excel Vers. 9) http://www.mvps.org/dmcritchie/excel/shortx2k.htm HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "klondy" <klondy@hotmail.com> wrote in message news:1008601c24f8c$2ccc08a0$39ef2ecf@TKMSFTNGXA08... > how can I add a worksheet in Excel 2000 using keyboard > shortcut |
| 6218 | If you're using the English language version of Excel, Alt+IW will do it. This the keyboard way of selecting the Insert * Worksheet command, and it's a lot easier to remember than Alt+Shift+F1!. In fact, if you get in the habit of using the keyboard for menu commands, you will ultimately save a lot of time. John Walkenbach For Excel tips, macros, & downloads... http://j-walk.com/ss "David McRitchie" <dmcritchie@msn.com> wrote in message news:uLEPm34TCHA.2556@tkmsftngp11... > Hi ......, > > Alt+Shift+F1 > > It is practically impossible to find a shortcut for something specific > using the Excel Help so here is a list of shortcuts that you can > use your browser's FIND to help you. > > Shortcut Keys in Excel 2000 (Excel Vers. 9) > http://www.mvps.org/dmcritchie/excel/shortx2k.htm > > HTH, > David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] > My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm > Search Page: http://www.mvps.org/dmcritchie/excel/search.htm > > "klondy" <klondy@hotmail.com> wrote in message news:1008601c24f8c$2ccc08a0$39ef2ecf@TKMSFTNGXA08... > > how can I add a worksheet in Excel 2000 using keyboard > > shortcut > > |
| 6451 | From Excel Help: Limited (read: only) by available memory -- Regards, Niek Otten Microsoft MVP - Excel "C Tate" <crtate@nobodyhere.tiscali.co.uk> wrote in message news:3d726a13$1_1@mk-nntp-2.news.uk.tiscali.com... > Does anybody know what the maximum number of sheets is in an Excel workbook? > > Thanks. > > |
| 6473 | Hi "C" Answer is "Limited only by available memory." But for all Excel Specifications, try using Help. The location of Specifications varies according to version of Excel but it's usually near the top of the Help Contents tree. You could try a search on *Excel Specifications* and that will give the details. I'm not being awkward! Just trying to facilitate your learning process. Don't listen to (often misinformed) views that Excel Help is written for Geeks by Geeks. Sure, there are limitations, but it has got a lot better over the years and is your first port of call. Keep posting! HTH -- Norman Harker Sydney, Australia Off-group contact welcomed as long as it's Excel related or sending money! "C Tate" <crtate@nobodyhere.tiscali.co.uk> wrote in message news:3d726a13$1_1@mk-nntp-2.news.uk.tiscali.com... > Does anybody know what the maximum number of sheets is in an Excel workbook? > > Thanks. > > |