Posts by fraudit

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Re: No suitable encoding when importing csv file

    Some update on it, equally strange for me...

    I still can't import the data manually, as the national characters are not being correctly displayed, BUT I've removed the VBA line related to text encoding and the file imports correctly!

    What's going on? :)

    I regularly import csv data dumps produced by one of the systems I use. They are all encoded in 65001 format in order to maintain proper displaying of national characters.

    All works smoothly on my professional laptop but today I need to take a quick look on the data dump on my private device (both run Excel 2016 EN-US). My macro has produced some error and debugging it showed encoding as the error source.

    Then I tried manual import but I got surprised seeing that I have only Macintosh and Windows ANSI encoding format available, no others!

    Since this is the same data dump as usual I don't think the problem is related to the file, it's rather something stuck to my private Excel installation. Can anyone advise me what to do in order to "regain" typical encoding standards?

    Re: Disable "We can't update some of the links..." message

    Thank you, again! That's in fact interesting...

    I've disabled this option, saved the file, reopened it and... The messages popped up again :( and when I checked options, the checkbox remained active.

    I repeated disabling it three times with no effect... :yawn: What's going on?

    Re: Text-To-Columns in macro converts 24 time into 12h format

    OK, now it's a bit better, as this macro followed by the NumberFormat code partly does the job. Thank you!

    I'm saying "partly" as the fixed width cannot really be applied here because the 1st column data is not really identical - see attached file - and that causes cutting some unnecessary marks from the date and putting them into time field. Thus it looks we should find a solution with delimited approach, though I don't really prefer that one over the other.

    Please see attached file - the original is CSV however the uploader engine kept rejecting it, so I've changed the extension into txt.

    Re: Text-To-Columns in macro converts 24 time into 12h format

    Thank you for suggesting that! Unfortunately the VB code doesn't work - I guess the reason is that the AM/PM marker is stored somehow in a separate column.

    My feeling is that I need some solution to convert the original mixed (date and time) column into two columns not three. But I have no idea what's behind the fact it splits one column into three while when doing the same manually it works correctly and splits data into two columns. Can anyone advise me on this strange behavior?

    I've built a Text-To-Columns function into my macro - it simply divides date and time stored in one column into two other columns, one for date and one for time.

    Strangely, when I perform this function directly in Excel, I receive the desired output: date + time in 24h format (as originally presented), but when I run exactly the same procedure recorded as a macro the output is: date + time in 12h format. An additional third column is created with AM/PM mark (in fact, data in a column that is supposed to stay unchanged is overwritten) - this column is not created when executing this function manually.

    Here's the code:

        Selection.TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
            Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
            :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

    Would you please advise me what to do in order to keep 24h time format?

    I believe I have a final question for today.

    I use fill down feature for columns that contains links to external data source, by combining IF and VLOOKUP funations:

    =IF(E5215="Declined";"-";IF(E5215="Abandoned";"-";IF(E5215="Project";"-";IF(E5215="Approved";"-";VLOOKUP(A5215;'D:\OPERATIONS\Collection\[Collection Database.xlsx]Collection Database'!$A:$M;13;FALSE)))))

    That's the VB code, if necessary:

    Set FraudRng = Range("L1").End(xlDown)
    FraudRng.Resize(1, 5).AutoFill Range(FraudRng.Address & ":P" & LastRow)

    Strangely for me if a given value is found in the reference sheet the fill down function works like a charm. However, when the Collection Database.xlsx is not up-to-date and some values listed in the active file can't be found in the reference file, I'm asked to open Collection Database.xlsx manually.

    Is there a way to get rid of this file open request? The standard Application.DisplayAlerts = False doesn't work for that...

    I need to search for particular values using VLookup in a separate file. From what I found on the web I believe it's not possible to do it with the reference file closed (am I right)?

    Thus I've come with the following code:

    Workbooks.Open FileName:="D:\OPERATIONS\Banki PL.xlsx", ReadOnly:=True
        Range("L2:L" & LastRow) = Application.WorksheetFunction.VLookup(Worksheets("Sheet1").Range("K2:K" & LastRow), _
        Workbooks("Banki PL.xlsx").Worksheets("Sheet2").Range("A:B"), 2, False)
        Workbooks("Banki PL.xlsx").Close

    The problem is that the target cells don't contain any values :(.

    Can you please advise me on that?

    Re: Use MID worksheet function with a range?


    I've finally ended with:

    Range("K2:K" & LastRow).FormulaR1C1 = "=MID(RC[-1],3,8)"
    Range("K2:K" & LastRow).Value = Range("K2:K" & LastRow).Value

    which is very close to what yoy suggest but misses the separate range setting.

    Re: Use MID worksheet function with a range?

    Hmmm... I understand I the expression Worksheets("Sheet1").Range("J2:J" & LastRow) will not be recognized as String and therefore the Mid formula won't execute, will it?

    I've tested the cell-by-cell solution but it takes several times longer to execute, thus I'm looking for a "bulk" solution.

    I need to extract a part of a bank account number - 8 chars, starting from 3rd place. The bank accounts number are in column J and I want column K to store 8-chars parts.

    I've set column K as text and column J as general format and I've created a following code:

    LastRow = ActiveSheet.UsedRange.Rows.Count 
    Set MidRange = Worksheets("Sheet1").Range("J2:J" & LastRow) 
    Range("K2:K" & LastRow) = Application.WorksheetFunction.Mid(MidRange, 3, 8)

    However it doesn't work :( and returns error 438 instead.

    I know I may do so by executing formula in cell K2 and then fill the active range down, but this takes longer to execute.

    Since I know the above solution is possible for VLookup function, I hope it may be also applied to Mid function.

    I've been trying to process the report with dates in format not automatically recognized by Excel, which is dd/mm/yyyy. I need a "classic" European yyyy-mm-dd format for my purposes.

    I'm trying to use TextToColum VBA function but have trouble to set up FieldInfo parameter. At the moment I have:

    With Range("F2:F" & Cells(Rows.Count, "F").End(xlUp).Row)
             .TextToColumns Destination:=Range("F2"), DataType:=xlDelimited, _
                           TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                           Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
                           FieldInfo:=Array(1, 5), TrailingMinusNumbers:=True
            .NumberFormat = "yyyy-mm-dd"
        End With

    but I'm receiving yyyy-dd-mm format as a result. Looked for a solution in Walkenbach book but haven't found one :(

    Can anyone advise me on the proper code to achieve the desired conversion?

    Many thanks for that!