Hard time to import a csv file into Excel for Mac.
I want to create a table ( not range ) that contains valid rows ( read, row where in a column the cell is f.e. not empty) reading the comma separated csv file.
At first I want to load the file in memory and filter it.
Most of the found samples do use libraries or Objects that are not available on the mac. Nothing found and working so far.
So I use the code that the macro recorder is creating, change it a little so it places it in the right sheet.
Most of the found samples do use libraries or Objects that are not available on the mac. Nothing found and working so far.
As other ways exist like you can find on any Excel forum
It should be obviously easier to choose a path to help you with an attachment
for the source csv text file and accordingly its exact expected result workbook,
just follow the top page yellow banner
Thank you for your reply. Its not that I'am not willing to upload a csv, but can you explain how this help with my problem that I can't open a file dialog on the Mac?
I'am curious on what 'any excel forum' I can find this solution?
Last edited by lord anubis; 04-30-2022 at 02:08 PM.
As it was for your second question, like you can see in post #3 link and, after all, we are not on some mind readers forum
Another way than FilterDialog is the VBA method GetOpenFilename.
Anyway just reading VBA help in particular for FileDialog or just from many samples within threads
so according to your attachment a VBA demonstration for a starting point :
PHP Code:
Sub Demo1() Dim V, F% With Application.FileDialog(3) .AllowMultiSelect = False .Filters.Clear .Filters.Add "csv text files", "*.csv" If .Show Then V = .SelectedItems(1) Else Exit Sub End With F = FreeFile Open V For Input As #F V = Split(Input(LOF(F), #F), vbLf) Close #F With [A1].Resize(UBound(V) + 1) .CurrentRegion.Clear .Value = Application.Transpose(V) .TextToColumns , 1, 1, , , , True .CurrentRegion.Columns.AutoFit End With Range("E2", [G1].End(xlDown)).HorizontalAlignment = xlRight End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Thank you again for your time but remember; again I'am using MacOS, and Application.FileDialog gives me nothing back.
In the supplied code it gives me 'error code 91'.
So the 'many examples' doesn't work. I got in my library I guess 30-40 samples working fine under Win but none under MacOS.
There is one piece of code that works, and thats using Applescript supplied by Ron de Bruin, but that doesn't work for csv files. BTW he did change his website, and its now even harder to find.
As yet written in post #5 there is another way than FilterDialog so did you try it at least, what did you have done so far ?
If nothing works then you should ask on some AppleScript forum
PHP Code:
Sub Demo2a() Dim V, R& V = Application.GetOpenFilename("Text files,*.csv"): If V = False Then Exit Sub R = FreeFile Open V For Input As #R V = Split(Input(LOF(R), #R), vbLf) Close #R For R = 1 To UBound(V) If V(R) Like """"",*" Then V(R) = False Next V = Filter(V, False, False) With [A1].Resize(UBound(V) + 1) .CurrentRegion.Clear .Value = Application.Transpose(V) .TextToColumns , 1, 1, , , , True .Parent.ListObjects.Add(1, .CurrentRegion, , 1).TableStyle = "TableStyleLight" & Application.RandBetween(8, 21) .CurrentRegion.Columns.AutoFit Range("E2:G" & .Rows.Count).HorizontalAlignment = xlRight End With End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
My code was a little longer, so I did fall back to your sample about building the Table.
I want to give the Table a Name for later use, in the hope to get less #ref error's in other sheets whom are referering to this table.
Also I want to extract the header names of the newly created table.
I thought that I could insert
in the with statement. Where importTableName is a String with the name. However, according to the namemanager, it gives only the first column a name, meaning the Table named "ImportTable" refers only to the first column.
Then I tried some other things, and came up with the following. Using your code I added...
About the Like, how should I read the """"",*". I understands that this is something like check everything in front of the first ',*' if it is a empty string. But is this something like """" & ",*", where the string's first part embedded in Quotation marks sits between Quotation marks and the second set of Quotation marks is used as an wildcard for the needed existing leftover? Why is there no & or + needed to connect them, the two strings? Where in the doc's did you find this?
The second question. The Filter documentation tells me that the second parameter should be required string, but False is Not a string. Not even an empty string. How did you come up or where did you find that you can use a Boolean here to compare a Array with a mixed set of strings and Booleans ( False )?
When a string contains a double quote, as a VBA string starts & ends with double quotes
so the double quote inside the string must be doubled in order VBA understands that is not the end of the string
but just a character so very not a wild card neither two strings
but the beginning of some lines of your csv text file so a single string
Well according to Filter VBA function some day I was lazy lucky !
As a boolean is easily transtyped to an integer or to a string
but rather than False you can obviously use some special character like € for example.
Bookmarks