Posts by Imbuzi

Re: Greek Characters Dont Display Properly

Font and cell size is the same. I have managed to fix the problem by amending my regional settings to Greek. it seems Ive found one of the quirks in dealing with different languages and alphabets in Excel.

Thanks for the suggestions

Hi All,
I cant seem to get an excel file that I have received from a Greek collegue to display Greek Characters properly.

Some of the lines in the file with Greek appear to be correct and have not been turned into gibberish.

In addition the cell properties of both the correctly displayed cell and the gibberish cell are exactly the same and in the cell format sample the gibberish is displayed as Greek Characters.

Ive tried formatting the corrupted cells with the uncorrupted cells format but that doesnt seem to have any effect.

TIA

Graeme

Re: One Spreadsheet No Data 8.7mb???

Do you have any formatting in the workbook that extends to the last row?

This may be a potential cause

Re: Trim A String In Vba

The completed string is required to be only 14 characters the users tend to only had spaces on the end of either the "corpAcct" or "Subdiv", thats why I was looking to use trim which if my understanding should do the trick.

Re: Trim A String In Vba

What I am trying to do is eliminate any spaces that are found in the string as there should be none.

Hope someone can help on this one.

I have written a function which works in the same way as the concatenate formula but where required it uses an underscore to make up the length (14 Characters) of the result. I seem however to have hit a minor snag when users input trailing spaces. I thought I could use trim to eliminate them but it doesnt seem to be working any ideas

TIA

Graeme

Function HypCon(CorpAcct, Subdiv)


Dim n, i, iLen, iLen2 As Integer
Dim sCorpAcct, sSubdiv As String
Dim iLen3, iLen4 As Integer
iLen = Len(Trim(CorpAcct))
iLen2 = 7
iLen3 = Len(Trim(Subdiv))
iLen4 = 5


'Determine the length of CorpAcct
    Select Case iLen
        Case Is <= iLen2
            i = Len(Trim(CorpAcct))
            sCorpAcct = Trim(CorpAcct)
        For n = i To iLen2
            sCorpAcct = sCorpAcct & "_"
        Next n
            Case Else
            sCorpAcct = CorpAcct
        End Select


'Determine the length of Subdiv
    Select Case iLen3
        Case Is <= iLen4
            i = Len(Trim(Subdiv))
            sSubdiv = Trim(Subdiv)
        For n = i To iLen4
            sSubdiv = sSubdiv & "_"
        Next n
            Case Else
            sSubdiv = Subdiv
        End Select
    
    
    HypCon = sCorpAcct & sSubdiv
End Function

Re: Will Tool Pack function travel and work with data file (.xls)?

You could include this in the ThisWorkbook object which would ensure that the addin is always present

Option Explicit
Const strAddin As String = "Analysis ToolPak"


Private Sub Workbook_Open()
With Application
   .StatusBar = ""
   .DisplayAlerts = False
End with
If Application.AddIns(strAddin).Installed = False Then
    Application.AddIns(strAddin).Installed = True
    
    If Application.AddIns(strAddin).Installed = True Then
        MsgBox "Excel Addin-'" & strAddin & "'." & Chr(13) & "Has been installed...", vbExclamation
    End If
End If
Application.DisplayAlerts = True


End Sub

Re: Cell summing, multi conditions

Your best bet would be to utilise a SUMIF Formula.

e.g. =SUMIF(A1:C10,"AddA",B1:C10)

However as you are using more than 1 condition for the summing I suggest that you insert and additional column into the sheet which includes an if statement along the lines of
=IF(C1>=0,"Add","")&trim(B1)

Re: MB xls file size best treatment

Try and not use to many sheets in the workbook as this can increase the size of the file.

Also check that the end of the used ranges within the worksheets does not exceed the area where you have data/formulae.

Only apply number formats to area that need it as this can also increase the size of the workbook.

HtH

Re: Amortisation using Actuarial method

Quote from Fin Fang Foom

I dont know this file could help you but it might get you started.

Thanks for the file but unfortunately the amortisation schedule that you posted isnt the actuarial method and isnt accurate enough. Thanks anyway though.

Well chaps its been a while since ive been on here and asked a question.

Ive been asked to build a spreadsheet that is able to amortise a sum of money using the actuarial method.

Only one problem is I havent got a clue where to start. is there any finance peeps that would be able to get me started?

TIA

Graeme

Eric,

I'm not sure if this will help but if you are running XP this might help

link

HTH

Graeme

Quote from Imbuzi

Hebrew Excelsite

AO is this your website???
HTH

Graeme

Michael,

One possible way you could get to switch between the languages is by using SendKeys. I havent used these for a while so you may need to get some help from some of the others but here goes...

Sub LanguageSwitch()
    Dim AltKey As String
    Dim CtrlKey As String
    Dim ShiftKey As String
    Dim TabKey As String
    Dim EnterKey As String
    '-----------------
    AltKey = "%"
    CtrlKey = "^"
    ShiftKey = "+"
    TabKey = "{TAB}"
    EnterKey = "~"
    
    
    SendKeys AltKey & ShiftKey, True




End Sub

HTH

Graeme

PS if you want to test the code you have to run it you cannot use debug mode to test it.

Michael,

You might want to have alook at this website it may be of use Hebrew Excelsite

HTH

Graeme

Workfrustration,

Have you not thought of saving your files as an addin. this will allow your users to utilise the file and you could then password protect the module to prevent tampering

HTH
Graeme

Or is this what you are after?

'32-bit API declarations
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
  Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long


Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long


Public Type BROWSEINFO
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszTitle As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
End Type


Sub SetProcessingDirectory()
'   For Excel 97 or later
    Dim Msg As String
    Msg = "Please select a location for the saving the files."
    FilePath = GetDirectory(Msg)
    If FilePath = "" Then
        MsgBox "Canceled"
    Else
        'MsgBox FilePath
    End If
End Sub




Function GetDirectory(Optional Msg) As String
    Dim bInfo As BROWSEINFO
    Dim path As String
    Dim r As Long, x As Long, pos As Integer
 
'   Root folder = Desktop
    bInfo.pidlRoot = 0&


'   Title in the dialog
    If IsMissing(Msg) Then
        bInfo.lpszTitle = "Select a folder."
    Else
        bInfo.lpszTitle = Msg
    End If
    
'   Type of directory to return
    bInfo.ulFlags = &H1


'   Display the dialog
    x = SHBrowseForFolder(bInfo)
    
'   Parse the result
    path = Space$(512)
    r = SHGetPathFromIDList(ByVal x, ByVal path)
    If r Then
        pos = InStr(path, Chr$(0))
        GetDirectory = Left(path, pos - 1)
    Else
        GetDirectory = ""
    End If
End Function

Sferical,

Is this the code that you are after:

Sub GetFileNameExample()
    Dim fNameAndPath As Variant
    fNameAndPath = Application.GetOpenFilename _
               (FileFilter:="Text Files (*.txt), *.txt", _
                Title:="Select File To Be Opened")
    If fNameAndPath = False Then _
                      Exit Sub
    MsgBox fNameAndPath
End Sub

HTH
Graeme

Kenshin,

Do a search in the help on Subtotal as a key word, this should give you what you are looking for

Graeme

Kenshin,

I would use a combination of both Access and Excel by holding the data in Access tables and then consolidating the tables using queries and then linking to the queries from Excel using a get external data link.

Depending on the amount of data you have i.e does the number of lines exceed th 65000 limit in Excel you may want to consider using SUMIF formulae in Excel.

TIA

Graeme