+ Reply to Thread
Results 1 to 19 of 19

Macos - Import CSV file using a filedialog

  1. #1
    Forum Contributor
    Join Date
    07-16-2009
    Location
    DutchLand
    MS-Off Ver
    Excel MacOS 16
    Posts
    161

    Macos - Import CSV file using a filedialog

    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.

    Please Login or Register  to view this content.

    However I don't want to use a hardcoded file, but can't choose a specific file in code by using Filedialog.

    This is my code, remember its on Excel for Mac. I get "Object variable or with block not set".

    Please Login or Register  to view this content.
    How do I get this to work?
    Please Login or Register  to view this content.
    Doesn't work either

    Second question - Is there a way to load it, the import, first in memory on the mac.

    As always thank you in advance,

    LA
    Last edited by lord anubis; 04-30-2022 at 06:42 AM.

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Macos - Import CSV file using a filedialog


    Quote Originally Posted by lord anubis View Post
    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 …

  3. #3
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Macos - Import CSV file using a filedialog


    A sample in this recent post …

  4. #4
    Forum Contributor
    Join Date
    07-16-2009
    Location
    DutchLand
    MS-Off Ver
    Excel MacOS 16
    Posts
    161

    Re: Macos - Import CSV file using a filedialog

    Hi Mark L,

    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?
    Attached Files Attached Files
    Last edited by lord anubis; 04-30-2022 at 02:08 PM.

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Try this …


    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 VF%
        
    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
             
    Split(Input(LOF(F), #F), vbLf)
             
    Close #F
        
    With [A1].Resize(UBound(V) + 1)
            .
    CurrentRegion.Clear
            
    .Value Application.Transpose(V)
            .
    TextToColumns 11, , , , 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 » !
    Last edited by Marc L; 04-30-2022 at 03:42 PM.

  6. #6
    Forum Contributor
    Join Date
    07-16-2009
    Location
    DutchLand
    MS-Off Ver
    Excel MacOS 16
    Posts
    161

    Re: Macos - Import CSV file using a filedialog

    Hi Marc L

    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.

    Other suggestions?

  7. #7
    Forum Expert
    Join Date
    10-11-2021
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    1,367

    Re: Macos - Import CSV file using a filedialog

    I am not familiar with mac but you could try application.getopenfilename

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Lightbulb Re: Macos - Import CSV file using a filedialog


    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 VR&
            
    Application.GetOpenFilename("Text files,*.csv"):  If False Then Exit Sub
            R 
    FreeFile
            Open V 
    For Input As #R
            
    Split(Input(LOF(R), #R), vbLf)
            
    Close #R
        
    For 1 To UBound(V)
            If 
    V(RLike """"",*" Then V(R) = False
        Next
            V 
    Filter(VFalseFalse)
        
    With [A1].Resize(UBound(V) + 1)
           .
    CurrentRegion.Clear
           
    .Value Application.Transpose(V)
           .
    TextToColumns 11, , , , True
           
    .Parent.ListObjects.Add(1, .CurrentRegion, , 1).TableStyle "TableStyleLight" Application.RandBetween(821)
           .
    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 » !
    Last edited by Marc L; 05-01-2022 at 09:08 AM.

  9. #9
    Forum Contributor
    Join Date
    07-16-2009
    Location
    DutchLand
    MS-Off Ver
    Excel MacOS 16
    Posts
    161

    Re: Macos - Import CSV file using a filedialog

    Quote Originally Posted by JEC. View Post
    I am not familiar with mac but you could try application.getopenfilename
    So did found some time. I did try a while back this call, but Excel did crashes; saw that the help file was updated recently; I came up with...

    Please Login or Register  to view this content.
    So do have some progress, the filter isn't working, but do get a 'decent' error back.
    Last edited by lord anubis; 05-01-2022 at 04:29 PM.

  10. #10
    Forum Contributor
    Join Date
    07-16-2009
    Location
    DutchLand
    MS-Off Ver
    Excel MacOS 16
    Posts
    161

    Re: Macos - Import CSV file using a filedialog

    Ah, I saw your reply a little late.
    Unfortunate the filter isn't working

    I like the

    Please Login or Register  to view this content.
    Didn't think about this yet.

  11. #11
    Forum Contributor
    Join Date
    07-16-2009
    Location
    DutchLand
    MS-Off Ver
    Excel MacOS 16
    Posts
    161

    Re: Macos - Import CSV file using a filedialog

    Hi Marc and others.

    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

    Please Login or Register  to view this content.
    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...

    Please Login or Register  to view this content.
    Now some finetuning.

    Anyways thank you.
    Last edited by lord anubis; 05-03-2022 at 10:19 AM.

  12. #12
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Macos - Import CSV file using a filedialog


    I never use a table name when the worksheet has only a single table so useless !
    Just using obviously its index # aka 1 to refer to this table …
    Last edited by Marc L; 05-03-2022 at 09:58 AM.

  13. #13
    Forum Contributor
    Join Date
    07-16-2009
    Location
    DutchLand
    MS-Off Ver
    Excel MacOS 16
    Posts
    161

    Re: Macos - Import CSV file using a filedialog

    Quote Originally Posted by Marc L View Post

    I never use a table name when the worksheet has only a single table so useless !
    Just using obviously its index # aka 1 to refer to this table …
    Never done that yet, will look into this. Thanks for the hint/tip!
    In this case I have a second table imported on the same sheet.

  14. #14
    Forum Contributor
    Join Date
    07-16-2009
    Location
    DutchLand
    MS-Off Ver
    Excel MacOS 16
    Posts
    161

    Re: Macos - Import CSV file using a filedialog

    Quote Originally Posted by Marc L View Post

    I never use a table name when the worksheet has only a single table so useless !
    Just using obviously its index # aka 1 to refer to this table …
    Never done that yet, will look into this. Thanks for the hint/tip!
    In this case I have a second table imported on the same sheet.

    And I need the name in some functions on different sheets. Maybe solvable with your index, but don't know that yet.

  15. #15
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Macos - Import CSV file using a filedialog


    According to my post #8 demonstration if an useless name is required :
    Please Login or Register  to view this content.

  16. #16
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Macos - Import CSV file using a filedialog


    According to my post #8 demonstration if an useless object variable is required :
    Please Login or Register  to view this content.

  17. #17
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Macos - Import CSV file using a filedialog


    According to your post #11 in order to check if the file to open has a csv extension :
    Please Login or Register  to view this content.
    Then your getFileExtension & validFileName are useless …

  18. #18
    Forum Contributor
    Join Date
    07-16-2009
    Location
    DutchLand
    MS-Off Ver
    Excel MacOS 16
    Posts
    161

    Re: Macos - Import CSV file using a filedialog

    Hello @Marc L.

    Thank you for the code advice.

    Two question about your code. It goes about the Like function

    Please Login or Register  to view this content.
    and the Filter function.

    Please Login or Register  to view this content.
    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 )?

  19. #19
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Macos - Import CSV file using a filedialog


    • 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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Batch import images into Excel on MacOs
    By Fabian451 in forum Excel General
    Replies: 1
    Last Post: 01-10-2021, 09:31 AM
  2. MSO FileDialog FilePicker and unsaved file
    By julhs in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-15-2016, 08:57 AM
  3. How to extract file name from user selected file via Application.FileDialog
    By Jpdatabase in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-09-2012, 04:45 AM
  4. FileDialog to Import XLS Content
    By KrisS in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-29-2010, 07:48 AM
  5. Return file name only from FileDialog
    By PMC1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-10-2006, 12:35 PM
  6. FileDialog to select file
    By Acid-Sky in forum Excel General
    Replies: 2
    Last Post: 09-12-2005, 02:05 PM
  7. Using FileDialog or GetOpenFileName To Allow File Creation
    By WhyIsDoug in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-28-2005, 02:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1