Replies

I don’t know where you’re looking to see “Auto recover save” but if you’ve got the “AutoSave” addin installed then this will do what you want (tools, addins, select autosave and then it will show up on the tools menu, about three down fromn the top.)

Alternatively, you may want to investigate pieterse‘s “Autosafe” addin, which gives you a lot more control.

I don’t know where you’re looking to see “Auto recover save” but if you’ve got the “AutoSave” addin installed then this will do what you want (tools, addins, select autosave and then it will show up on the tools menu, about three down fromn the top.)

Alternatively, you may want to investigate pieterse‘s “Autosafe” addin, which gives you a lot more control.

Not a coding method for vba as such, but if Unkamunka’s solution doesn’t work for you, this came up somewhere else very recently (VB/VBA forum?) Paste the following code into notepad and save it as “check_Excel.vbs” to your desktop. double clicking will run the file – as long as vbs scripting is enabled. the method is courtesy of Rob Bruce. Note that it will also grab visible instances.

FWIW I now have one for word, access, powerpoint and excel on my desktop.

‘################################## CODE START
‘ XLcheck.vbs
‘ Find an invisible instance of Excel
‘ from Rob Bruce (http://www.rb-ad.dircon.co.uk)

Dim objXL, strMessage

On Error Resume Next

‘ Try to grab a running instance of Excel:
Set objXL = GetObject(, “Excel.Application”)

‘ What have we found?
If Not TypeName(objXL) = “Empty” Then
strMessage = “Excel Running.”
Else
strMessage = “Excel Not Running.”
End If

‘ Feedback to user…
MsgBox strMessage, vbInformation, “Excel Status”

‘ Make it show so we can kill it
if strMessage = “Excel Running.” then objXL.Visible = true

‘ End of VBS code
‘########################### CODE END

Not a coding method for vba as such, but if Unkamunka’s solution doesn’t work for you, this came up somewhere else very recently (VB/VBA forum?) Paste the following code into notepad and save it as “check_Excel.vbs” to your desktop. double clicking will run the file – as long as vbs scripting is enabled. the method is courtesy of Rob Bruce. Note that it will also grab visible instances.

FWIW I now have one for word, access, powerpoint and excel on my desktop.

‘################################## CODE START
‘ XLcheck.vbs
‘ Find an invisible instance of Excel
‘ from Rob Bruce (http://www.rb-ad.dircon.co.uk)

Dim objXL, strMessage

On Error Resume Next

‘ Try to grab a running instance of Excel:
Set objXL = GetObject(, “Excel.Application”)

‘ What have we found?
If Not TypeName(objXL) = “Empty” Then
strMessage = “Excel Running.”
Else
strMessage = “Excel Not Running.”
End If

‘ Feedback to user…
MsgBox strMessage, vbInformation, “Excel Status”

‘ Make it show so we can kill it
if strMessage = “Excel Running.” then objXL.Visible = true

‘ End of VBS code
‘########################### CODE END

I don’t believe it is possible – though I may be wrong. However, if you’re happy with using a VBA solution, the following comes courtesy of Rob Bovey

Public Sub PrintCellComments()
''' Excel Utilities

I don’t believe it is possible – though I may be wrong. However, if you’re happy with using a VBA solution, the following comes courtesy of Rob Bovey

Public Sub PrintCellComments()
''' Excel Utilities

Did you try changing the line:

“Error Source: ” & Err.Source & Chr(3) & Chr(3) & _

to

“Error Source: ” & Err.Source & Chr(13) & Chr(13) & _

– that works for me on the code you posted.

Sherry,

Variables such as lrow can be defined as having a certain type – string or integer for instance. declaring them enables vba to work faster with them – if you don’t then it assigns the variable type variant, which takes slightly longer to work with. Each type of variable has it’s advantages and disadvantages. Very broadly speaking, the advantage is what it allows you to do and the disadvantage is the amount of memory declaring it as that type costs. Long is a numeric variable type that has the range (2,147,483,648) to 2,147,483,647 (to quote help). I would have declared lrow as type integer until about a year ago, but integer types only have a range from (32,768) to 32,768 – making it conceivable that you could (on occaision) run into problems in the routine under discussion – added to which, discussion in the lounge seems to indicate that integer really isn’t worth bothering with.

In the absence of any other comments from others here who have a better grasp on these things than me, point vba help at the phrase ‘Data Type Summary’ – enter ‘data types’ in the index, click display and then look for ‘data type summary’.

HTH

Brooke

The most likely scenario is that a macro you were running did not reset this setting after turning it on, either by poor design or due to crashing. I think that there is another scenario, in which a workbook with this option on will affect all others if it is opened first – similar to the way the application caculate setting is sometimes stumbled into – though I’m not 100% sure about that.

The most likely scenario is that a macro you were running did not reset this setting after turning it on, either by poor design or due to crashing. I think that there is another scenario, in which a workbook with this option on will affect all others if it is opened first – similar to the way the application caculate setting is sometimes stumbled into – though I’m not 100% sure about that.

My first instinct would be to make sure that Tools | Options | General | Settings | Ignore other applications is unchecked. However, that is based on Excel 2K, not 2003.

My first instinct would be to make sure that Tools | Options | General | Settings | Ignore other applications is unchecked. However, that is based on Excel 2K, not 2003.

Silverback,

Here’s a little something to play with. Not much automation to worry about, I’m afraid. The subtotals are pulled in using sumifs, and the categories on each sheet are restricted using data validation linked to the named range on the totals sheet – to add a new category, you can simply insert a column anywhere between the current columns C to I. I kept the monthly sheets to a minimum for size reasons – all you’ll need to do after inserting a new sheet and labelling it, eg “mar”, is to copy the february formulae down and replace the “feb” in the formulae with “mar”.

This approach does mean you don’t have your subtotals on the monthly sheets, but hopefully it will get you started.

Silverback,

Here’s a little something to play with. Not much automation to worry about, I’m afraid. The subtotals are pulled in using sumifs, and the categories on each sheet are restricted using data validation linked to the named range on the totals sheet – to add a new category, you can simply insert a column anywhere between the current columns C to I. I kept the monthly sheets to a minimum for size reasons – all you’ll need to do after inserting a new sheet and labelling it, eg “mar”, is to copy the february formulae down and replace the “feb” in the formulae with “mar”.

This approach does mean you don’t have your subtotals on the monthly sheets, but hopefully it will get you started.

Given Shades answer, all I can do is post you a sample of essbase 5 code – we aren’t on six yet, so you’re probably going to have to do somealtering. Hopefully, however, it will get you started. In version 5 there isn’t a macro recorder as you seem to indicate, and excel will not record essbase code through it’s macro recorder.

‘####################################################################
Sheets(“Total Europe”).Select

x = EssVConnect(Empty, “User Name”, “Password”, “Server”, “Application”, “Database”)

Application.GoTo Reference:=”Tot_PL_Ord”
x = EssMenuVRetrieve()
If x = 0 Then intchkgd = intchkgd + 1 Else intchkbd = intchkbd + 1

Application.GoTo Reference:=”Tot_PL_Rev”
x = EssMenuVRetrieve()
If x = 0 Then intchkgd = intchkgd + 1 Else intchkbd = intchkbd + 1

x = EssVDisconnect(Empty)
‘####################################################################

Note that all the commands are EssV***** – they may be EssVI***** for version 6 but a check of the help file will tell you for sure. FWIW, I have found the Helpfiles to be very user-friendly in comparison to other applications helpfiles.

some comments to help with the above – again, all with reference to version 5.

the connection line has the arguments passed in as you see them in the logon/connection box. be careful of the fifth – “Application” – as if you define it as a variable at the top of your code it will interfere with your intellisense. Arbour defined the name of it as Application and so excel gets confused between that and it’s own application object.

there are two retrievals here: all you have to do is select the area of the retrieval and then call the EssMenuRetrtieve function. the value passed back is o if succesful and 1 if not: this allows you to keep a tally of how many successful/unsuccessful retrievals have occured.

Always disconnect – this stops essbase getting muddled if you have connections to more than one database open.

finally, you will need to declare the functions. again, the helpfile will show you how to do this, but the three I’ve used above are demonstrated below. These should go at the top of the module before any sub or function.

Declare Function EssVConnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, _
                                                    ByVal username As Variant, _
                                                    ByVal password As Variant, _
                                                    ByVal server As Variant, _
                                                    ByVal EssbaseApp As Variant, _
                                                    ByVal database As Variant) _
                                                    As Long
Declare Function EssVDisconnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) _
                                                    As Long
Declare Function EssVRetrieve Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, _
                                                    ByVal range As Variant, _
                                                    ByVal lockFlag As Variant) _
                                                    As Long

Note that I have actually changed the name of the parameter “Application” to “EssbaseApp” in the connection function to get around the problem I mentioned earlier.