Posts by Epidemic

  • Importing a CSV file with dates in European format

    Re: Importing a CSV file with dates in European format


    Actually the file is supposed to be is a csv, however, I forgot I was having a problem with that and did change the CSV files extension to Txt for the above code to work.


    The problem with .CSV file is that when I open it, it treats the date as American format converts days with the first 2 characters of 01-12 to months. Changing it to TXT allows excel to treat the DMY format as it should be treated then as soon as it opens as written it adjusts it to MDY format in keeping with Local.


    Attempting to use a formula like =TEXT(A1,"mm/dd/yyyy") would not work after the file opens because only ~60% of the records end up being text format. the rest are corrupted date serial numbers.


    Using your SO macro on the text file imports does not do the text to columns running it on CSV file corrupts the dates.



    In short, I want to open a .csv file containing records with European date format DMY and have the date remain a valid serial number date. When I open the CSV, dates like 30/06/2015 is treated as a string and the next record 01/07/2015 is treated as a date but with the wrong month. it is the variable nature of how it is treating these dates that is causing me touble.

  • Importing a CSV file with dates in European format

    Re: Importing a CSV file with dates in European format


    I think I found a solution but I don't understand it


    the sub called open text sub was recorded using text import where I told it to import the file with the third field in day month year format. the file opens fine. But in trying to understand the limitations of the macro I decided to try and simplify it. So I created a sub called OpenTXTsimplified ()



    Code
    Sub OpenTextSimplified()
        Workbooks.OpenText Filename:="C:\Users\vahlek\Desktop\EXPORT1438638298826 - Copy.txt", Origin:=437, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
        Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 4)), TrailingMinusNumbers:=True
End Sub


    the simplified Macro seems to work seems to work as well but I don't see where it defines Day Month Year or the column to apply DMY format.


    Example dataset

    Code
    Name,Address,Date,Number1,Number2,Number2...
Bill, Oak St, 30/06/2015, 100, 200, 300
Cindy,Ocean Dr, 29/06/2015,200,100,400




    Can anyone explain to me what these subs do and why they are able to convert the Euro date in column 3 to American style?

  • Importing a CSV file with dates in European format

    Re: Importing a CSV file with dates in European format


    No I have not. How does that work. wouldn't I need to specify a region/local I am not sure what I am saying true to there.


    I would need it to read the file as if it was Europe and then via command then tell it to put it in US standard.

  • Importing a CSV file with dates in European format

    Re: Importing a CSV file with dates in European format


    I thought this would be an easy one for someone. Perhaps I did not explain my problem.


    I have a macro that opens up a CSV file form another software package. This CSV file is used to generate graphs based upon dates. I live in the USA and all my graphs prefer US dating scheme. One of the columns in my csv file is in day month year format is there a way to have excel get the date serial number or simply treat it as text.


    I don't particularly want to do any iterative process that requires correcting each date on each line as this will slow my process quite a bit.

  • Importing a CSV file with dates in European format

    I am trying to open a CSV file and the dates are in European format. This causes my macro to have all sorts of problems.



    Is there a way to open the file and have excel know that this is European format? Then allow me to convert from European to US. I could probably use some form of text conversion but the result of opening the file leaves a mix of dates and strings depending on whether the first number is between 1 and 12 or 13 to 31.

  • Open file error




    I am trying to open a workbook, when I attempt to open it I know some of the files will not exist at this point, I need the macro to skip over the next dozen or so steps and go to the next loop. However when I run it and it finds no file it alerts the user rather than going onto the next file.

  • Subscript error


    on the first loop populating Frmla(frmcount) I get a subscript error. I really do not have a clue why. any suggestions



    A little more about the failure. I ran the macro once with it dimensioned wrong I had Frmla (1 to 24) and got a subscript error on loop 25. I corrected dimension Frmla (1 to 31). Ran it again and Then I started getting subscript errors on loop one where I populate Frmla.


    I closed the macro, reopened it and edited the dimension of Frmla and then ran it and it runs fine. Am I not clearing Frmla completely or something?

  • Code jumps out for some reason to previous Subroutine.

    Re: Code jumps out for some reason to previous Subroutine.


    oops,


    I tried your code it still is a not able to run unless I have the sheet active.


    Code
    With Sheets("magicindex") 
    FirstRow = .Row 
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row 
    With Range(.Cells(2, 3), .Cells(LastRow, 3)) 
        .FormulaR1C1 = "=MID(RC[-2],1,LEN(RC[-2])-1) & 1" 
        .Value = .Value 
    End With 
End With

  • Code jumps out for some reason to previous Subroutine.

    Re: Code jumps out for some reason to previous Subroutine.


    Thank you. I am still not sure why the first method did not work but this one does work. I make some work arounds where I activated the sheets and jump around. I will go back to try and incorporate your code as it is much cleaner.



    Again thank you.

  • Code jumps out for some reason to previous Subroutine.

    Re: Code jumps out for some reason to previous Subroutine.


    Ok this works sorta, I am not sure why but it appears that the sheet has to be active to function. I thought the with sheets statements should take care of that. I get a 1004 error until I make the sheet magic index sheet active. it fails at the


    With .Range(Cells(2, 3), Cells(LastRow, 3))



  • Code jumps out for some reason to previous Subroutine.

    I have been trying to figure out why my macro jumps to the former subroutine when I perform this line of code.


    Sheets("MagicIndex").Selection.Copy
    Sheets("magicindex").Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlPasteSpecialOperationAdd


    I simply want to make the selected formulas into values.



  • Change the x axes on all charts.

    Re: Change the x axes on all charts.


    Ok This is my Newest code. I don't know how to do this if my sheet is inactive but If the macro is launched on my active sheet and I "select" everything I need to, it works. I don't particularly like having to select but this seems to at least function although not as efficiently as I would like.


  • Change the x axes on all charts.

    Re: Change the x axes on all charts.


    BTW this is the recorded macro


    Code
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlCategory).CategoryType = xlCategoryScale
    Selection.TickLabels.NumberFormat = "m/d H"


    '*** This would be used to change the graphs back ***
    'ActiveChart.Axes(xlCategory).Select
    'ActiveChart.Axes(xlCategory).CategoryType = xlTimeScale
    'Selection.TickLabels.NumberFormat = "m/d;@"


    I want to automate this to change all Graphs on my sheet. I have tried so many permutations of this that I am beginning to wonder if Excel can't do what i ask!!!

  • Change the x axes on all charts.

    When the user changes they source data from days to hours I need to have the macro make change the axes to reflect the change in some 12 separate graphs.


    Below is my entire code thus far. Of course it fails as everything I ever do with objects fails. I Can't get it through my thick head how these things work.


    I would appreciate if someone could tell me what I am doing wrong. (well besides trying to program:))


    Code
    With Sheets("Interface")
    For Each Chart In .ChartObjects
        With ChartObjects.Axes
        .CategoryType = xlCategoryScale
        .TickLabels.NumberFormat = "m/d H"
        End With
    Next Chart
End With

  • Why cant I directly set Wrap on a range of cells?

    I would like to simply reference this range of cells and change one formatting attribute.


    Code
    Sheets("flaggedSite report").Range(Cells(RowV, 2), Cells(RowV, 6)).WrapText = True



    I get a runtime error 1004 with this???

    Code
    With Sheets("FlaggedSite Report").Range(Cells(RowV + 1, 2), Cells(RowV + 1, 5))
        '.Range(Cells(RowV, 2), Cells(RowV, 6)).WrapText = True
        .WrapText = True
    End With



    This one runs but gives me unpredicted results. This one I think changes the format on my original sheet vs FlaggedSite Report


    Code
    With Worksheets("FlaggedSite Report")
        With Range(Cells(RowV + 1, 2), Cells(RowV + 1, 5))
            .WrapText = True
        End With
    End With


    All I want to do is with as small a code as possible make a change to an inactive sheet of my choosing to the wrap format of a range of cells.

  • how can I make a range of cells the same on an inactive sheet using cells function.

    Re: how can I make a range of cells the same on an inactive sheet using cells functio


    Quote from Luke M;717777

    How about this?

    Code
    Sheets("GS 1").Range("D4:H10").Copy Sheets("flaggedSite report").Cells(lastrow + 1, 1)


    That would literally copy the cells. If you just want to copy the values


    Code
    Sheets("flaggedSite report").Cells(lastrow + 1, 1).Resize(1,5).Value = Sheets("GS 1").Range("D4:H10").Value


    This copies just the values. The Resize is needed to make the output range = size of input range.


    Very intersting. I will play with these that copy resize thing sounds like what I need.


    Although I think alketraz's Idea is good as well. Thank your folks for your assist I would be coding right now but my machine is angry with me and excel is not responding after I did a resume this morning after my computers night time sleep. :)