Posts by robinh

    thanks tom.

    The following VBA was the solution that I found.

    Sub datecorrects()

    Dim c As Variant



    Set rangetoalter = Selection
    For Each c In rangetoalter

    '0.03653 chosen as this is slightly more than a leap year and ensures
    'that date will be in the correct year.

    If IsNumeric(c.Value) And Right(c.Value, 1) = 0 And c.Value > 19000000 Then
    c.Value = (c.Value - 19000000) * 0.03653
    End If
    'the macro will crash if on a date formatted square. Also some columns have pathology results in thus the number has to be greater than 1900. The error has occured if the right digit is a 0

    Next c
    End Sub

    i am sure this is not very elegant but it works

    Can anyone help me.

    I have to import data from various General Practice computer systems to help UK GPS analyse their patients. The data is held on .csv files on floppy disks.

    The date formats are general EDIFACT style ie 19970101. It is better to import theses dates and convert to excel 1990 dates as this format (35431 or 1/1/1997).
    is easy to present data in

    This bit is easy: the following macro imports from a floppy and formats the correct columns to year month day.

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;A:\CHDREP1.CSV", _
    .Name = "CHDREP1"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = xlWindows
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False

    'it is important the the below row match, 5= date, 1 = number.
    '................................a b c d e f g h i j k l m n o p q r s t u
    .TextFileColumnDataTypes = Array(1, 5, 1, 1, 1, 5, 1, 5, 1, 5, 1, 5, 1, 5, 1, 5, 1, 5, 1, 5, 1, _
    5, 1, 5, 1, 5, 1, 5, 1, 5, 1, 5)
    'v w x y z a b c d c d

    The problem is that in some systems when only the year of a diagnosis is known 1997 is represented as 19970000 and the dates corrupt as in the example attached. The only way I can think of to correct this is to write a macro to search all the above dates at the end and if the dates are > 19000000 (ie corrupted) then perform the following function
    (c.value-19000000)*0.3653 on the squares.

    How would you correct the data on the attached spreadsheet (which is an example of what the dates look like after importing.


    Dr Robin H

    I need to paste results of a workbook to a worksheet called CHD in another workbook called
    C:\PRIMIS\masterfiles\Results.xls" This is the solution I have chosen. However it doesn't work if C:\PRIMIS\masterfiles\Results.xls" is already open.

    How do I get around this? Presumably I could put another If statement in with statement to close C:\PRIMIS\masterfiles\Results.xls" first if it is already open but what is the VBA command? Maybe there is a better solution?

    ( results already in windows paste)

    ChDir "C:\PRIMIS"
    Workbooks.Open Filename:="C:\PRIMIS\masterfiles\Results.xls"
    End If


    Robin H

    Thanks Doug,

    My program loads from a CD Rom
    CHDGENERIC. It then imports .csv data from a floppy (from ten different types of GP computer system) and then standardises the data using macros and VBA for GPS and staff to target patients with Coronary Heart Disease.

    There is no problem if the file is saved with the same file name but to a different folder (ie not the CD ROM) . However if the file is given a different name such as CHDRESULTS the macros within the workbook which sort patients eg into those with heart failure or those who have had heart attacks won't work as the macro applications alter from eg

    Application.Run "Macro31" in the original
    Application.Run "CHDGENERIC.xls!Macro31" in the new file. CHDGENERIC of course no longer exists as the CDROM has been removed

    Should I alter the naming of my Subs from MacroX to something else.

    is this enough

    regards robin

    I have a file called CHDGENERIC.xls!

    Can anyone tell me how to ensure that my my macros will still run when I save the file under a different name eg


    Application.Run "Macro31"

    will not work when the spreadsheet is saved with a different name as VBA looks for and can't find it as the new application directory is

    Application.Run "CHDGENERIC.xls!Macro30"


    Can anyone help me?
    I need to put several buttons into the toolbar when my excel workbook is opened. ( I presume this is the most elegant way) This is because my workbook has several sheets and I want any of the macros to be launched from any page. I can put one button on, as in this example from the help files but I want to put several customised buttons/bars on.
    1. How do I put more than one custom bar on the toolbar?
    2. How do I put more than one button on in the custombar?
    3. How do I put on a customised button? ie a picture that I have created. I can do this by “selecting customise/commands/macros/ selecting the smiley face and then right clicking and redrawing” but how can I save this image so that it loads each time I open this spreadsheet as in the example below
    Example from previous newsletter
    Dim bClosed As Boolean

    Private Sub Workbook_Activate()
    On Error Resume Next
    Application.CommandBars("MyCustomBar").Enabled = True
    On Error GoTo 0
    End Sub

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    bClosed = Not Cancel
    End Sub

    Private Sub Workbook_Deactivate()
    On Error Resume Next
    If bClosed = False Then 'Only decativating
    Application.CommandBars("MyCustomBar").Enabled = False
    Else 'Workbook closing
    End If
    On Error GoTo 0
    End Sub

    Private Sub Workbook_Open()
    Dim cContPop As CommandBarPopup
    Dim cCont As CommandBarButton
    Dim cComm As CommandBar

    On Error Resume Next
    Set cComm = Application.CommandBars.Add

    With cComm
    .Name = "MyCustomBar"
    .Position = msoBarTop
    .RowIndex = 1
    .Visible = True
    .Protection = msoBarNoCustomize
    Set cContPop = .Controls.Add(Type:=msoControlPopup)
    End With

    With cContPop
    .Caption = "MyMacros"
    Set cCont = .Controls.Add()
    End With

    With cCont
    .Caption = "Do It"
    .Style = msoButtonIconAndCaption
    .FaceId = 387
    .OnAction = "MyMacro"
    End With
    On Error GoTo 0
    End Sub

    Thanks Robin H

    I have an excel spreadsheet that imports .csv files and formats dates, sorts the data into columnse etc. The whole process takes about 2-3 minutes.

    What is the best way of hiding the spreadsheet so that the user doesn't go cross-eyed during the macro processing? eg is it possible to load a form onto the spreadsheet to hide the processing by stating " please wait will the results being analysed"