Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

override locale computer settings

I appreciate any help. This is a problem I posted yesterday probably without
being very clear.

How can I override the Windows Regional settings so that my Excel File looks
the same in a computer in Italy and in the Netherlands without forcing the
user to change the Locale Regional settings?
This affects the Date and the Time format, specially within formulas.

Also it seems that the locale setting on Cell Format only works if the
locale is NOT the default.
i.e. A file with the default in UK Eng. and a cell date in Italian and
another in UK Eng, will have the cell in Italian on a Dutch computer but the
UK one will be turned into Dutch.

Besides, this setting does not work within a cell.
i.e. the formula TEXT(E12,"*yymmdd")&"-Blabla" will look like 050216-Blabla
on a UK Eng, but will look yy0216-Blabla on a Dutch or Italian computer

I appreciate any help. This is a problem posted yesterda


Post your answer or comment

comments powered by Disqus
I have the following formula TEXT(E34,"yymmdd")&"-"&$C$5&"-DrawingList.PDF
It work ok on my computer, but on my colleague's there is some general
language setting that makes excel only recognise "jjmmdd". We cannot find
immediatly the setting (all the software is in English) and anyway we would
prefer not to change it.
How can we make sure the formulas work independently of the local computer
settings?
Thanks?

I have the following formula TEXT(E34,"yymmdd")&"-"&$C$5&"-DrawingList.PDF
It work ok on my computer, but on my colleague's there is some general
language setting that makes excel only recognise "jjmmdd". We cannot find
immediatly the setting (all the software is in English) and anyway we would
prefer not to change it.
How can we make sure the formulas work independently of the local computer
settings?
Thanks?

Dear all,

Is it possible to make Excel file or userform to open only in local computer?

The Userform should open when a computer or user name is "Accounts-01".
This can be good to keep official data secure.

Any one please help me in this regard. Thanks in Advance.

Regards
Charles

Hi,

I got the following code from Leith Ross. The macro is wonderfull and working good.
What I want is, Just need to add "Hi" in the body of the message

the complete code is below

Code:
 'Written: March 31, 2008
'Author:  Leith Ross
'Summary: Sends out emails from a worksheet list. The message body is on
'         a separate worksheet. This worksheet is copied to a file in
'         HTML format. The file is opened an copied as a string which
'         becomes the message body in Outlook.

'Used to find the Outlook icon in the system tray. If present then Outlook is running
 Private Declare Function FindWindow _
   Lib "user32.dll" _
     Alias "FindWindowA" _
       (ByVal lpClassName As String, _
        ByVal lpWindowName As String) As Long

Sub EmailFromWorksheet()
 
  Dim FSO As Object
  Dim HTMLcode As String
  Dim HTMLfile As Object
  Dim Msg As String
  Dim myInstance As Boolean
  Dim olApp As Object
  Dim olEmail As Object
  Dim olPID As Long
  Dim R As Long
  Dim TempFile As String
  Dim Wks As Worksheet
  
  'Outlook constants aren't available using late binding
   Const olByValue = 1
   Const olCC = 2
   Const olFolderContacts = 10
   Const olMailItem = 0
   Const olFormatHTML = 2
   
    'Starting Row of Email Data
     R = 2
     
    'Set some program variables
     TempFile = "C:ROI APMyEmail.htm"
     Set Wks = Worksheets("Body of the Message")
     
    'Start Outlook if it isn't running
     If FindWindow("Outlook Notification Area Icon Window", vbNullString) = 0 Then
        myInstance = True
         olPID = Shell("C:Program FilesMicrosoft OfficeOffice11OUTLOOK.exe", 2)
     End If
     
    'Assign variable to the running instance
     Set olApp = GetObject("", "Outlook.Application")
     
    'Trap any errors
     On Error GoTo CleanUp

      'Stop the email loop if cell is blank
       Do While Cells(R, "A")  ""
       
         'Add URL to the Body of the Message worksheet
          'Wks.Cells(1, "A") = Cells(R, "C").Text
          Wks.Hyperlinks.Add Anchor:=Wks.Cells(1, "A"), Address:= _
     Cells(R, "C").Text, _
    TextToDisplay:=Cells(R, "C").Text
         'Convert the Message worksheet into HTML
          With ActiveWorkbook.PublishObjects.Add( _
            SourceType:=xlSourceRange, _
            FileName:=TempFile, _
            Sheet:=Wks.Name, _
            Source:=Wks.UsedRange.Address, _
            HtmlType:=xlHtmlStatic)
           .Publish (True)
          End With
       
         'Read the HTML file back as a string
          Set FSO = CreateObject("Scripting.FileSystemObject")
          Set HTMLfile = FSO.GetFile(TempFile).OpenAsTextStream(1, -2)
            HTMLcode = HTMLfile.ReadAll
            HTMLfile.Close
          HTMLcode = Replace(HTMLcode, "align=center x:publishsource=", _
                      "align=left x:publishsource=")
                      
         'Compose the email
          Set olEmail = olApp.CreateItem(olMailItem)
            With olEmail
              .To = Cells(R, "A").Text
              .Subject = Cells(R, "B").Text    'Voucher Number
              .BodyFormat = olFormatHTML
        .Attachments.Add ("in.tesco.orgdfsrootFinancial ServicesROI Accounts PayableROI APROI Email ProgramSend Individual
EmailsMultiline coding Sheet.xls")
              .HTMLBody = HTMLcode
              .Send
            End With
          
        'Increment Row counter
         R = R + 1
        'Delete the tempoary file
         Kill TempFile
        'Delete the Published Object
         With ActiveWorkbook.PublishObjects
           .Item(.Count).Delete
         End With
      'Get the next email cell
       Loop
   
CleanUp:
  'Close Outlook instance if this macro created it
   If myInstance = True Then TerminateProcess olPID
  'Was there an error
   If Err  0 Then
    'Delete the Temp File
     If Dir(TempFile)  "" Then Kill TempFile
    'Delete the Publish Object
     With ActiveWorkbook.PublishObjects
       If .Count  0 Then .Item(.Count).Delete
     End With
   End If
   
  'Free memory resources
   Set olApp = Nothing
   Set olEmail = Nothing
   Set FSO = Nothing
   
End Sub

Public Sub TerminateProcess(ByVal PID As Long)

  Dim colProcessList As Object
  Dim objProcess As Object
  Dim objServices As Object
  Dim ProcessRetVal As Long
  
  'Connect to the WMI namespace through the local computer "."
    Set objLocator = CreateObject("WbemScripting.SWbemLocator")
    Set objServices = objLocator.ConnectServer(".", "rootcimv2")
    
   ' Terminate the Application by its Process ID
     Set colProcessList = objServices.ExecQuery _
     ("SELECT * FROM Win32_Process WHERE ProcessId =" & Str(PID))
     
   ' WMI requires a loop even if there is only one object
     For Each objProcess In colProcessList
       ProcessRetVal = objProcess.Terminate()
       If ProcessError(ProcessRetVal) Then Exit For
     Next objProcess

CleanUp:
  Set objLocator = Nothing
  Set objServices = Nothing
  Set colProcessList = Nothing
  Set objProcess = Nothing
  
End Sub

Private Function ProcessError(ByVal Err_Value As Long) As Boolean

  Dim Msg As String
  
  If Err_Value = 0 Then Exit Function

ProcessError:
   Select Case Err_Value
    Case 2
      Msg = "Access Denied"
    Case 3
      Msg = "Insufficient Privilege"
    Case 8
      Msg = "Unknown failure"
    Case 9
      Msg = "Path Not Found"
    Case 21
      Msg = "Invalid Parameter"
  End Select
  
  MsgBox Msg, vbexcalamtion, "WMI Win32_Process"
  ProcessError = True
  
End Function
I tried here to add Hi but I am not getting the result
Code:
 to
Code:
can some one help me on this...

I have posted this question in
http://www.excelforum.com/excel-prog...ml#post1959252

Hi,

I have the following code to send automatic email to n number of people at one short.

I have 3 columns in excel.
Column A - Email addres
Column B - Some numbers ( subject)
Column C - Some Links ( body of the message)

wen i run the macro, email goes to all the email address in column A with the subject in column B and with the links in column c respectively and adds the body of the message in sheet 2.

the program is working fine. I just want to add the code to attach an excel file to all these emails.
Assume I have the excel file in My C drive my documents folder.

The code is below

Thanks to leith for providing this code earlier.

'Written: March 31, 2008
'Author: Leith Ross
'Summary: Sends out emails from a worksheet list. The message body is on
' a separate worksheet. This worksheet is copied to a file in
' HTML format. The file is opened an copied as a string which
' becomes the mesaage body in Outlook.

'Used to find the Outlook icon in the system tray. If present then Outlook is running
Private Declare Function FindWindow _
Lib "user32.dll" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Sub EmailFromWorksheet()

Dim FSO As Object
Dim HTMLcode As String
Dim HTMLfile As Object
Dim Msg As String
Dim myInstance As Boolean
Dim olApp As Object
Dim olEmail As Object
Dim olPID As Long
Dim R As Long
Dim TempFile As String
Dim Wks As Worksheet

'Outlook constants aren't available using late binding
Const olByValue = 1
Const olCC = 2
Const olFolderContacts = 10
Const olMailItem = 0
Const olFormatHTML = 2

'Starting Row of Email Data
R = 2

'Set some program variables
TempFile = "C:MyEmail.htm"
Set Wks = Worksheets("Body of the Message")

'Start Outlook if it isn't running
If FindWindow("Outlook Notification Area Icon Window", vbNullString) = 0 Then
myInstance = True
olPID = Shell("C:Program FilesMicrosoft OfficeOffice11OUTLOOK.exe", 2)
End If

'Assign variable to the running instance
Set olApp = GetObject("", "Outlook.Application")

'Trap any errors
On Error GoTo CleanUp

'Stop the email loop if cell is blank
Do While Cells(R, "A") ""

'Add URL to the Body of the Message worksheet
Wks.Cells(1, "A") = Cells(R, "C").Text

'Convert the Message worksheet into HTML
With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
FileName:=TempFile, _
Sheet:=Wks.Name, _
Source:=Wks.UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

'Read the HTML file back as a string
Set FSO = CreateObject("Scripting.FileSystemObject")
Set HTMLfile = FSO.GetFile(TempFile).OpenAsTextStream(1, -2)
HTMLcode = HTMLfile.ReadAll
HTMLfile.Close
HTMLcode = Replace(HTMLcode, "align=center x:publishsource=", _
"align=left x:publishsource=")

'Compose the email
Set olEmail = olApp.CreateItem(olMailItem)
With olEmail
.To = Cells(R, "A").Text
.Subject = Cells(R, "B").Text 'Voucher Number
.BodyFormat = olFormatHTML
.HTMLBody = HTMLcode
.Send
End With

'Increment Row counter
R = R + 1
'Delete the tempoary file
Kill TempFile
'Delete the Published Object
With ActiveWorkbook.PublishObjects
.Item(.Count).Delete
End With
'Get the next email cell
Loop

CleanUp:
'Close Outlook instance if this macro created it
If myInstance = True Then TerminateProcess olPID
'Was there an error
If Err 0 Then
'Delete the Temp File
If Dir(TempFile) "" Then Kill TempFile
'Delete the Publish Object
With ActiveWorkbook.PublishObjects
If .Count 0 Then .Item(.Count).Delete
End With
End If

'Free memory resources
Set olApp = Nothing
Set olEmail = Nothing
Set FSO = Nothing

End Sub

Public Sub TerminateProcess(ByVal PID As Long)

Dim colProcessList As Object
Dim objProcess As Object
Dim objServices As Object
Dim ProcessRetVal As Long

'Connect to the WMI namespace through the local computer "."
Set objLocator = CreateObject("WbemScripting.SWbemLocator")
Set objServices = objLocator.ConnectServer(".", "rootcimv2")

' Terminate the Application by its Process ID
Set colProcessList = objServices.ExecQuery _
("SELECT * FROM Win32_Process WHERE ProcessId =" & Str(PID))

' WMI requires a loop even if there is only one object
For Each objProcess In colProcessList
ProcessRetVal = objProcess.Terminate()
If ProcessError(ProcessRetVal) Then Exit For
Next objProcess

CleanUp:
Set objLocator = Nothing
Set objServices = Nothing
Set colProcessList = Nothing
Set objProcess = Nothing
End Sub

Private Function ProcessError(ByVal Err_Value As Long) As Boolean
Dim Msg As String
If Err_Value = 0 Then Exit Function

ProcessError:
Select Case Err_Value
Case 2
Msg = "Access Denied"
Case 3
Msg = "Insufficient Privilege"
Case 8
Msg = "Unknown failure"
Case 9
Msg = "Path Not Found"
Case 21
Msg = "Invalid Parameter"
End Select
MsgBox Msg, vbexcalamtion, "WMI Win32_Process"
ProcessError = True
End Function

I have List of email address in Column A,
Vouchers numbers in Column B
URLs in Column C
body of the message in 2nd sheet.

when I run a macro an email has to be sent to each persons email address listed in column A
with Voucher # in Column B in the subject line and URL from Column C in the body of the message
after the URL, the body of the message will remain same for all the emails:

I have the code for the above function, the only proble which I have is, all the emails are automatically going to the conserned persons email address,
They are Receiving Voucher # on the subject line, URL in the body of the message and rest of the message after the URL (which was saved in 2nd sheet)

But they are all receiving the URLs as text and not as link

Can you please help me out how to work on this.

The code as follows

Code:
'Written: March 31, 2008
'Summary: Sends out emails from a worksheet list. The message body is on
'         a separate worksheet. This worksheet is copied to a file in
'         HTML format. The file is opened an copied as a string which
'         becomes the mesaage body in Outlook.

'Used to find the Outlook icon in the system tray. If present then Outlook is running
 Private Declare Function FindWindow _
   Lib "user32.dll" _
     Alias "FindWindowA" _
       (ByVal lpClassName As String, _
        ByVal lpWindowName As String) As Long

Sub EmailFromWorksheet()
 
  Dim FSO As Object
  Dim HTMLcode As String
  Dim HTMLfile As Object
  Dim Msg As String
  Dim myInstance As Boolean
  Dim olApp As Object
  Dim olEmail As Object
  Dim olPID As Long
  Dim R As Long
  Dim TempFile As String
  Dim Wks As Worksheet
  
  'Outlook constants aren't available using late binding
   Const olByValue = 1
   Const olCC = 2
   Const olFolderContacts = 10
   Const olMailItem = 0
   Const olFormatHTML = 2
    
    'Starting Row of Email Data
     R = 2
     
    'Set some program variables
     TempFile = "C:MyEmail.htm"
     Set Wks = Worksheets("Body of the Message")
     
    'Start Outlook if it isn't running
     If FindWindow("Outlook Notification Area Icon Window", vbNullString) = 0 Then
        myInstance = True
         olPID = Shell("C:Program FilesMicrosoft OfficeOffice11OUTLOOK.exe", 2)
     End If
     
    'Assign variable to the running instance
     Set olApp = GetObject("", "Outlook.Application")
     
    'Trap any errors
     On Error GoTo CleanUp

      'Stop the email loop if cell is blank
       Do While Cells(R, "A")  ""
       
         'Add URL to the Body of the Message worksheet
          Wks.Cells(1, "A") = Cells(R, "C").Text
          
         'Convert the Message worksheet into HTML
          With ActiveWorkbook.PublishObjects.Add( _
            SourceType:=xlSourceRange, _
            FileName:=TempFile, _
            Sheet:=Wks.Name, _
            Source:=Wks.UsedRange.Address, _
            HtmlType:=xlHtmlStatic)
           .Publish (True)
          End With
       
         'Read the HTML file back as a string
          Set FSO = CreateObject("Scripting.FileSystemObject")
          Set HTMLfile = FSO.GetFile(TempFile).OpenAsTextStream(1, -2)
            HTMLcode = HTMLfile.ReadAll
            HTMLfile.Close
          HTMLcode = Replace(HTMLcode, "align=center x:publishsource=", _
                      "align=left x:publishsource=")
                      
         'Compose the email
          Set olEmail = olApp.CreateItem(olMailItem)
            With olEmail
              .To = Cells(R, "A").Text
              .Subject = Cells(R, "B").Text    'Voucher Number
              .BodyFormat = olFormatHTML
              .HTMLBody = HTMLcode
              .Send
            End With
          
        'Increment Row counter
         R = R + 1
        'Delete the tempoary file
         Kill TempFile
        'Delete the Published Object
         With ActiveWorkbook.PublishObjects
           .Item(.Count).Delete
         End With
      'Get the next email cell
       Loop
   
CleanUp:
  'Close Outlook instance if this macro created it
   If myInstance = True Then TerminateProcess olPID
  'Was there an error
   If Err  0 Then
    'Delete the Temp File
     If Dir(TempFile)  "" Then Kill TempFile
    'Delete the Publish Object
     With ActiveWorkbook.PublishObjects
       If .Count  0 Then .Item(.Count).Delete
     End With
   End If
   
  'Free memory resources
   Set olApp = Nothing
   Set olEmail = Nothing
   Set FSO = Nothing
   
End Sub

Public Sub TerminateProcess(ByVal PID As Long)

  Dim colProcessList As Object
  Dim objProcess As Object
  Dim objServices As Object
  Dim ProcessRetVal As Long
  
  'Connect to the WMI namespace through the local computer "."
    Set objLocator = CreateObject("WbemScripting.SWbemLocator")
    Set objServices = objLocator.ConnectServer(".", "rootcimv2")
    
   ' Terminate the Application by its Process ID
     Set colProcessList = objServices.ExecQuery _
     ("SELECT * FROM Win32_Process WHERE ProcessId =" & Str(PID))
     
   ' WMI requires a loop even if there is only one object
     For Each objProcess In colProcessList
       ProcessRetVal = objProcess.Terminate()
       If ProcessError(ProcessRetVal) Then Exit For
     Next objProcess

CleanUp:
  Set objLocator = Nothing
  Set objServices = Nothing
  Set colProcessList = Nothing
  Set objProcess = Nothing
  
End Sub

Private Function ProcessError(ByVal Err_Value As Long) As Boolean

  Dim Msg As String
  
  If Err_Value = 0 Then Exit Function

ProcessError:
   Select Case Err_Value
    Case 2
      Msg = "Access Denied"
    Case 3
      Msg = "Insufficient Privilege"
    Case 8
      Msg = "Unknown failure"
    Case 9
      Msg = "Path Not Found"
    Case 21
      Msg = "Invalid Parameter"
  End Select
  
  MsgBox Msg, vbexcalamtion, "WMI Win32_Process"
  ProcessError = True
  
End Function


I have to share a workbook between French and English Canada location users.
I want the date format to respect ISO YYYY-MM-DD format independantly if you
look or print the worksheet in English or in French. But when on an English
set-up computer it changes to respect location set-up define by regional
settings and translate the date format to MM-DD-YYYY? I tried to use a Custom
formatting string but does not appear to override the regional settings? I
there any hope in what I am trying to achieve?

We have an excellent in house developed add-in(xlam) that automate many processes.
Unfortunately we have some excel templates with the same UDF functions locally, i.e

	VB:
	
 
    rng = Sqr(dx ^ 2 + dy ^ 2) 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The above function exists in both add-in and local workbook.
In Excel 2003 this was not a problem.
But in Excel 2007 the formula is referenced to the add-in (xlam) and not the local workbook VBA i.e

	VB:
	

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
.Is there a method for setting the priority of which the formula is referenced?

I have tried disabling the add-in in the workbook_open() procedure and enable on the workbook_Beforeclose() without luck.

I've done some research and I have not found any solution so I am positing it here to see if it is REALLY impossible before I give up

I have a macro that reads some cells coming in a "dd mmm yyyy" format as a string, where mmm are the three-letter abreviation in ENGLISH. To read it I replace spaces as hyphons and use CDate. No problem here, since I set my system with english formats.

however when run from a spanish locale computer Cdate fails to convert it to date.

is there any function that can do the trick?

the macro involves numerous date comparisons, so better if it is something simple

thanks!

Hi folks, (using Excel 2010 / Win 7) I have vba code in which date values are stored in an array of type variant. These values are published to a new worksheet using the resize property of the Range object: eg:

	VB:
	
(myArray(), 2))) 
 
rng.Value = myArray    rng.Value = myArray 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
On one computer (used for testing the code) it publishes the date-value according to the local-computer’s date format. E.g. 3/02/2012 (this is what I want)

- The problem is, that on another computer, however, it publishes the date format as a serial number e.g. 40942.

Note YYYYMMDD date values are calculated and stored in the VBA Array according to the commands below, so the date-values are stored in the array in the date-format of the local computer (Variant/Date e.g. #02-12-2010#) .


	VB:
	
yearX = Left(Trim(dateY), 4) 
 
monthX = Mid(dateY, 6, 2) 
dayX = Mid(dateY, 9, 2 
dateY = DateSerial(yearX, monthX, dayX) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
As far as I can determine, the regional settings on both computers are the same. Also, the worksheets are new so they have no pre-formatted cells. Many thanks in advance for your advice,
Peter.

Hello,

Consider the following string-variable which has text assigned as follows:

ClippedStr = "Feb 24 2012 00:37:16 EST"
(always the same structure as above; another example: "Mar 22 2012 00:45:34 EST"

What I'm seeking is a RegEx pattern (or other appropriate VBA Expression - however RegEx gives me flexibility in case ClippedStr has more extraneous data) to extract the date from this string. However, I need the extraction to precisely allocate each element of ClippedStr as follows:

YearX = "2012"
MonthX = "Feb"
DayX = "24

That way, I can assign a date in the format of the local computer using:
DateX = DateSerial(yearX, monthX, dayX)

Here's the code which I need your ideas:


	VB:
	
 Extract_Date() 
     
    Dim YearX As String, MonthX As String, DayX As String, ClippedStr As String 
    Dim DateX As Date, RE As Object, REMatches As Object 
     
    ClippedStr = "Feb 24 2012 00:37:16 EST" 
    Set RE = CreateObject("vbscript.regexp") 
     
    With RE 
        .Global = True 
        .IgnoreCase = True 
         '.Pattern = ?????    '

Hi,
I am trying to write to the event log from VBA. I have managed to write to the Event Log(You need to start the NT Applet, Run - EVENTVWR.EXE) using the following article:
http://support.microsoft.com/?kbid=154576......Since we're using VBA, there is no application.logevent, so you need to use the example shown for VB 4. However the log looks like below as I seem to be missing a registry setting.
Do you know what registry setting I need to get this to work?(Clean error/warning message)
I am using VBA to write out the Log (Excel 2001 and VB 6.3)

EVENT VIEWER: APPLICATION LOG
The description for Event ID ( 1001 ) in Source ( Project1 ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. The following information is part of the event: Information from Project1.

Hi All

i am facing a strange problem, the below code works fine in one computer and gives error in another computer.

Both the computers have EXCEL 2007, both have installed the OBIEE office feature in the computer.

Set cbTaskPane = Application.CommandBars("Oracle BI EE")
cbTaskPane.Controls("Refresh &All").Execute

do you know what we are missing,

thanks

Hi there,

i have a little problem with the performance of my script.

I have lots of excel workbooks, one of which is a list that extracts data from all the other files. Now the problem is, that all those files (and the list) are on a computer in the network. If I now open the list and update it, it accesses all the other files from my local computer over the network, what slows everything down.

Is there some way i can make the remote computer run the vba script instead of running it from my local computer? Ideally this event is triggered by my computer, so i can control when the script is run. For example a button in the list "update", that then makes the remote computer update the list.

Any help would be appreciated..

Thank you very much

Dear Excel problemsolvers,

This is a follow-up question from an earlier array-problem, which was solved.

Here is the new, more complex problem. Assume that I have a set of stockprices that goes up and down, for simplicity I will assume no decimals in prices or price movements (example below).

AB1Stock priceDesired result21053114411451236150710281209100105121117312200

Further assume that we want excel to begin by comparing A2 to A3. If A3 minus A2 is smaller than 1, it should continue searching down the column until the greatest value is found BEFORE a price decline (trend break).

So first at A5 the criteria is fufilled since 12-10=2 which is greater than 1. However, in A6 we have an even greater increase 15-10=5 before the price decline. So consequently, I want A2 to A5 to be subtracted from A6 since is is the greatest value (and greater than each of the numbers +1)

So far, I can get excel to do this by using the formulas (under the condition that the table ENDS here!!).

Spreadsheet FormulasCellFormulaB2{=MAX(MAX(IF($A$2:$A$7>=A2+2,$A$2:$A$7))-A2,0)}B3{=MAX(MAX(IF($A$2:$A$7>=A3+2,$A$2:$A$7))-A3,0)}B4{=MAX(MAX(IF($A$2:$A$7>=A4+2,$A$2:$A$7))-A4,0)}B5{=MAX(MAX(IF($A$2:$A$7>=A5+2,$A$2:$A$7))-A5,0)}B6{=MAX(MAX(IF($A$2:$A$7>=A6+2,$A$2:$A$7))-A6,0)}Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

So my problem is that I would like excel to recognice the local maximums in the different sets in the price table, and not to continue searching cells below that particular local maximum.

SET 1 A2 to A6 with a local maximum of 15.
SET 2 A7 to A8 with a local maximum of 12
SET 3 A10 to A12 with a local maximum of 20

The local miniamas can e.g. assume the value of 0 (as in the example)

So to sum up: My key question here is how I get excel to understand that it should not look for the global maximum in the table, but recognize when a trend is broken and a local maximum comes to an end, and make the proper calculations within the SET (trend, local maxima) and not compare it to the entire dataset?

I have been struggling with this problem for so long, I appreciate any help I can get

Best,

Cphdk

Hi everyone, this is the complete code of the Macro that is giving me trouble.

I have 2 copies of a workbook that have to be identical. One is the Supervisor copy, stored on a local Computer and used for updating Productinformation & prices as well as adding new products, the other copy is a public copy on a server that is used by various department to record sales and payment information and to produce reports.

The following code has two problems. Please see the remarks in the code and the explanation below.

Just a few remarks:

Called Functions / Subs:
Adimrecht is a function that gives (adminrecht(true)) or takes (..(false) adminrights of the file to the sub.

workwith( SheetName as String, OnOff as Boolean) opens / closes and unprotects / protects a sheet for handling through code.

IsOpen(FullFileName as String) is a function by Ivan F. Moala www.excelfiles.com/isfileopen.html#anchor_37

all called subs and functions work just fine.

Variables:

DieseDatei = ThisWorkbook.name
Blatt = Worksheet

Code:
Private Sub ButtonUpdateSystem_Click()
Dim fs, folder, myfolder
Dim TargetFile
Dim TargetfileString As String

Dim DieseDatei
Dim a
Dim Blatt As Object
Dim Trgtblatt
On Error GoTo 2

DieseDatei = ThisWorkbook.Name
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.EnableEvents = False

Call workwith("analisis costos", True)
Call workwith("Usarlo solo", True)
Call workwith("Service Info", True)
Call workwith("LP REP", True)
Call workwith("LP G E", True)
Call workwith("LP G A", True)
Call workwith("LP G I", True)
Call workwith("LP G H", True)


Set folder = CreateObject("Scripting.FileSystemObject")
Set myfolder = folder.GetFolder("H:RESPALDOTODOSEXCURS")
Set TargetFile = CreateObject("Scripting.FileSystemObject")
TargetFile = "H:RESPALDOTODOSEXCURSexcurs.xls"
TargetfileString = "H:RESPALDOTODOSEXCURSexcurs.xls"

If IsOpen(TargetfileString) = False Then
    Application.AutomationSecurity = msoAutomationSecurityForceDisable
    Workbooks.Open Filename:=TargetFile
    Application.AutomationSecurity = msoAutomationSecurityByUI
    
    With TargetFile
1*     Call workwith("analisis costos", True)
            Workbooks(DieseDatei).Sheets("Analisis costos").Cells.Copy
            Range("A1").PasteSpecial xlPasteAll
            Cells(1, 1).Select
        Call workwith("analisis costos", False)
        
1*    Call workwith("Usarlo solo", True)
            Workbooks(DieseDatei).Sheets("Usarlo solo").Cells.Copy
            Range("A1").PasteSpecial xlPasteAll
            ActiveWorkbook.ChangeLink Name:="Andrest2.xls", NewName:="H:RESPALDOTODOSEXCURSEXCURS.XLS", _
            Type:=xlExcelLinks
            
            Cells(1, 1).Select
        Call workwith("Usarlo solo", False)
        
        Call workwith("Service Info", True)
            Workbooks(DieseDatei).Sheets("Service Info").Cells.Copy
            Range("A1").PasteSpecial xlPasteAll
            Cells(1, 1).Select
        Call workwith("Service Info", False)
        
        Call workwith("LP REP", True)
            Workbooks(DieseDatei).Sheets("LP REP").Cells.Copy
            Range("A1").PasteSpecial xlPasteAll
            Cells(1, 1).Select
        Call workwith("LP REP", False)
        
        Call workwith("LP G E", True)
            Workbooks(DieseDatei).Sheets("LP G E").Cells.Copy
            Range("A1").PasteSpecial xlPasteAll
            Cells(1, 1).Select
        Call workwith("LP G E", False)
        
        Call workwith("LP G A", True)
            Workbooks(DieseDatei).Sheets("LP G A").Cells.Copy
            Range("A1").PasteSpecial xlPasteAll
            Cells(1, 1).Select
        Call workwith("LP G A", False)
        
        Call workwith("LP G I", True)
            Workbooks(DieseDatei).Sheets("LP G I").Cells.Copy
            Range("A1").PasteSpecial xlPasteAll
            Cells(1, 1).Select
        Call workwith("LP G I", False)
        
        Call workwith("LP G H", True)
            Workbooks(DieseDatei).Sheets("LP G H").Cells.Copy
            Range("A1").PasteSpecial xlPasteAll
            Cells(1, 1).Select
        Call workwith("LP G H", False)
        
2*         Do While Workbooks("excurs").Sheets.Count  Workbooks(DieseDatei).Sheets.Count
             If Workbooks("Excurs").Sheets.Count < Workbooks(DieseDatei).Sheets.Count Then
                   For a = Workbooks(DieseDatei).Sheets.Count To 1 Step -1
                       Set Blatt = Workbooks(DieseDatei).Sheets(a)
                        On Error GoTo 4
                            Set Trgtblatt = Workbooks("excurs").Sheets(Blatt.Name)
                            GoTo 5
4                           Application.AutomationSecurity = msoAutomationSecurityForceDisable
  Executes and Stops here-> Workbooks("excurs").Sheets.Add
after:=Workbooks("excurs").Sheets(Workbooks("excurs").Sheets.Count)
                                Workbooks("Excurs").Sheets(Sheets.Count).Name = Workbooks(DieseDatei).Sheets(a).Name
                            Application.AutomationSecurity = msoAutomationSecurityByUI
                           Exit For
5                   Next a
            End If
       Loop
        
        ActiveWorkbook.Save
        ActiveWorkbook.Close
        
    End With
Else
    MsgBox "Sistema esta usado" & vbCr & "Por favor intente mas tarde.", vbOKOnly, "Error"
End If
GoTo 99
2   MsgBox "Hay un error. Informacion no actualisado."
    
99  Sheets("blank").Visible = True

    Call workwith("analisis costos", False)
    Call workwith("Usarlo solo", False)
    Call workwith("Service Info", False)
    Call workwith("LP REP", False)
    Call workwith("LP G E", False)
    Call workwith("LP G A", False)
    Call workwith("LP G I", False)
    Call workwith("LP G H", False)
    
    Set myfolder = Nothing
    Set folder = Nothing
    Set TargetFile = Nothing
        
    adminrecht (False)
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    On Error GoTo 0


End Sub
Problems:

1* For some reason excel pastes to "Analisis Costos" without linking formulas to the source book (supervisor copy), but puts links in "Usarlo solo". Both sheets contain many formulas. I do not want the links to be inserted - so I am removing them after pasting in "Usarlo Solo". The only difference I can see between the 2 Sheets is that "Usarlo solo contains hidden cells and is split by freeze Panes, while "Analisis Costos" has no split but hidden cells.

2* This Loop is supposed to check if the sheets.count is the same in both files. If not it should insert a new sheet in the mastercopy, identify which one is missing, rename it to be identical with the supervisor copy and copy all contents of the supervisor copy to the public copy.
For some reason the code simply stops after the indicated line when I go through it line by line with F8. I do not receive any error message. The behavior of the VBA editor after this line is as if I had pressed the STOP button. e.g. Code execution is ended and the sub is left.

Any help / ideas and wild accusations of being stupid are welcome and appreciated.

I have posted the question regarding error 2 before here and here, but not gotten any answers

I have two data/time series that I need to work on together.

One is continuous 30 minute intervals around the clock and the other is about 14, 30 minute data points per day.

The continuous data is a result field or formula and the other is static. I need to merge them together in a way that I can continue to manipulate the formula.

I think the data from the continuous time series needs to be called into the sheet dependent on the discontinuous one based on date and time.

Anybody know the best way to merge the two so the continuous/ computed set is still hot linked to the source sheet?

In my code below I have this line of code
Case "TD"
strfilename = "TomsblackibmTomsProposals" & strfilename I would like to send this file by email if possible instead of to his computer. The user recently went from a desktop "in the office" to a laptop "all over the place".

Here is my code
Code:
Sub Save_and_SaveSalesman()


Dim strPath As String, strPath2 As String, CurrPath As String
    
    Dim WB1 As Workbook
    Dim WB2 As Workbook
        
    Set WB1 = ActiveWorkbook
    
    'First thing, save my work
    WB1.Save
    
    CurrPath = WB1.Path
    
    'ASSUMING THAT C6 and O3 are BOTH in WB1
    'move this line HERE: only do this once, and concatenate in the Select..Case later
    'doing thsi inside the Select..Case pulls values from WB2, which might cause errors...
    strfilename = Range("C6").Value & Range("O3").Value & ".xls"
    
    strPath = "C:Documents and SettingsOwnerMy DocumentsCompleted Proposals"
    
    strPath2 = "C:Documents and SettingsOwnerMy DocumentsSurface Systems"
    
    On Error Resume Next
    
    'I then want to save my file as "Proposal" and the number in Cell O3
    WB1.SaveAs Filename:=strPath & strfilename
    
    On Error GoTo 0
    
    'I call this workbook "new_file"
    'as long as you use the WB1 object, you should not need to store the name... - PES
'    new_file = wb1.Name
    
    'you should never need to select anything... - PES
'    Range("F2").Select
    
    'I want to open "Proposal for XL" so I can make a 2nd copy to the salesmans computer
    Set WB2 = Workbooks.Open(Filename:=strPath2 & "Proposal for XL.xls")
    
    'I have to save the "new_file"
    'WHY??? you did this above - PES
'    Workbooks(new_file).Save
    
    'Workbooks(new_file).Close
    'Here is where i need to choose the computer for it to go to. As well as give the file a name that the salesman
recognizes. C6 is customer name and O3 is the proposal number
    'Select Case WB2.Sheets("FRONT").Range("C2").Value
    Select Case WB1.Sheets("FRONT").Range("C2").Value
        Case "MD"
            strfilename = "MIKESRGATEWAYMikesProposals" & strfilename
            
        Case "TD"
            strfilename = "TomsblackibmTomsProposals" & strfilename
            
        Case "DJ"
            strfilename = "DAVEJONESDavesProposals" & strfilename
            
        Case "CP"
            strfilename = "Chuckscomputerdaily" & strfilename
            
    End Select
    
    WB1.SaveCopyAs Filename:=strfilename
    
    WB1.ActiveSheet.Shapes("Button 53").Visible = False
    
    ChDir CurrPath
    
    Application.ScreenUpdating = True
    
    WB1.Close
End Sub


Thank You,
Michael

Hi all,

I use excel as a front end to produce hundreds of dashboards which are then emailed out to staff.

There are two hyperlinks on the top of each dashboard - one is a mailto with my project's email address, the other is a hyperlink to an interactive powerpoint show (pps) in kiosk mode which works as a help file.

The pps sits on a shared drive which everybody has read access to.

The hyperlink works fine locally, but not when emailed out.

This is because excel insists on turning my hyperlink into a relative one rather than absolute, so when the dashboard is launched from a user's email system, the link no longer points at the file.

I need the hyperlink to be absolute, but can't find out a way of setting this for the link or for the workbook.

Changing the computer settings will not be an option as I'd have to do it for all 400 recipients.

Anybody got any bright ideas?

Cheers.

Tom.

I have found that using a macro to set a cell value to a date does not
generate the expected result. Consider ...

Sub mcrSetDate()
ActiveCell.Value = "1/2/2003"
ActiveCell.NumberFormat = "dd mmm yyyy"
End Sub

This results in "02 Jan 2003". However if I enter "1/2/2003" into a cell
directly and format it I get "01 Feb 2003". Clearly VBA is not observing
the system date/time format which is set to Australian. (ie. dd/mm/yy) but
it is clearly parsing the .Value to a date serial.

Changing the statement to the following corrects the problem...

ActiveCell.Value = DateValue("1/2/2003")

However this means because my macro is dealing with data of unknown type
(from a text file) I have to inspect every value to decide if it is a date
so I can cast it before assigning it to the .Value.

Does anyone know of a cleaner way to get around this problem. The sheet
involved is always new and has never had any formatting aplied by the time
the data is being placed on it.

Cheers, Frank.

Hello All,

First of all, my sincere apologies for having the clock on my computer set
wrong. I was changing my clock to see if an Excel spreadsheet was correctly
calculating with dates, and I forgot to switch it back to the correct date.
I wasn't intentionally trying to jump in line. Sorry about that!

Secondly, I was able to solve the problem that had me stumped for quite a
while.

Thanks,
Danno

Hi,

Can someone help me. I wrote some VBA Code with my computer settings in
english and some of my colleagues have the one in French.

My code for the date is not translating and I would like to improve my code
to avoid my colleagues to have to change it manually.

Is there a code to add to my own to convert french into english when
executing my code ?

Thanks in advance and regards,

I have a folder with pictures and another folder with Excell spreadsheet in
which I have hyperlinks defined to the pictures in the pictures folder. I am
using Office 2003. I am experiencing a very strange behaviour:

a) I can open my Excell file from two other computers on my network and when
I click on hyperlink the pictures they display O.K. I can also add new
hyperlinks pointing to the pictures and again they work fine.

b) when I open the same Excell file on my local computer and try to click on
the same hyperlink nothing happens - no errors no pictures displayed

c) again on my local computer5, when I try to add a new hyperlink in the
same spreadsheet pointing to the same folders with pictures it seems to
define it correctly, but when I click on hyperlink, nothing happens again
(no errors and the picture does not display correctly)

What could be causing this problem?

Thanks

Office 2007 Excel problem with windows system local

There is a problem with MS office 2007 Excel ! when i set my windows local setting from "Control Panel ->change keyboard and other input methods ->Administrative Tab ->Change system local" and set to persian everytime I'm closing the Excel it's Not Responding and all of my added data is gone !!

Please Help me

Thanks
Microsoft Certified System Engineer 2003


No luck finding an answer? You could always try Google.