Posts by fraudit

fraudit · Oct 28th 2015

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?

fraudit · Oct 28th 2015

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?

fraudit · Oct 14th 2015

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

This one does the job perfectly

Code

Sub Auto_Open()
     
    Dim wb As Workbook
     
    Set wb = Workbooks.Open("C:\PathToMyFile", UpdateLinks:=False)
    
    ThisWorkbook.Close SaveChanges:=False
     
End Sub

fraudit · Oct 14th 2015

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

All right! I'm using shortcut anyway to open this particular file, so the code is as good as it can be!

Many thanks for your support!

fraudit · Oct 14th 2015

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?

fraudit · Oct 14th 2015

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

Thanks, but I'm familiar with this one and it doesn't affect the mentioned message.
It refers only to those links that may be updated automatically, not to those that cannot

fraudit · Oct 14th 2015

Is there a way to somehow get rid of this message (see the well-known screenshot below)?

I'm aware of its root cause. My file contains links to other closed files, so not all data can be updated. I'm aware of it and I'm happy with it, so what's the point of being bothered with this message?

Many thanks for any ideas!

[ATTACH=CONFIG]67186[/ATTACH]

fraudit · Jul 9th 2015

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

Understood! Many thanks for your help!

I will go for the suggested solution as the file you had attached proves it works like a charm!

fraudit · Jul 9th 2015

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

Oh, yes, apologies for the missing info!

I'm importing the files using coma as a delimiter. The respective column to be split is L, as it presents the time a transaction was completed.

fraudit · Jul 9th 2015

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.

fraudit · Jul 9th 2015

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?

fraudit · Jul 8th 2015

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:

Code

Columns("G:G").Select
    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?

fraudit · Apr 15th 2015

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:

Code

=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:

Code

Range("L1").End(xlDown).Select
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...

fraudit · Apr 15th 2015

Re: Problem with VBA Vlookup to another file

OK, I've found out it again by myself... I need to activate the main window first...

fraudit · Apr 15th 2015

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:

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?

fraudit · Apr 15th 2015

Re: Use MID worksheet function with a range?

Thanks!

I've finally ended with:

Code

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.

fraudit · Apr 15th 2015

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.

fraudit · Apr 15th 2015

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:

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.

fraudit · Apr 14th 2015

Re: Change date format using TextToColumns

Wow, I've just realized I need to state Array(1, 4) to reflect proper date format.

Hopefully it'll help someone else. Worth look at: https://msdn.microsoft.com/en-…rary/office/ff193593.aspx

fraudit · Apr 14th 2015

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:

Code

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!