+ Reply to Thread
Results 1 to 7 of 7

Editing a recorded macro of a csv import error

  1. #1
    Registered User
    Join Date
    03-07-2014
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    7

    Editing a recorded macro of a csv import error

    Let's say I have a valid .csv file path in A1 and I want to write a macro that imports whatever file is in A1. What I did was to record a macro and browsed for the specific file to see the code, then edit it such that the file path is the one in cell A1 but it's giving me a 424 error. Here's what I'm working with:

    With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;selected file path" _ {changed this to "TEXT;"&Cells(1,1) _}
    , Destination:=Range("$A$1"))
    .Name = "selected file name" {What can I change this to such that it's the file name of the file path in A1? (this can vary based on user input)}
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = True
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
    End With

    Any help would be greatly appreciated.

  2. #2
    Registered User
    Join Date
    03-07-2014
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Editing a recorded macro of a csv import error

    bump bump bump

  3. #3
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

    Re: Editing a recorded macro of a csv import error

    Not sure about your recorded Macro.. but try this..

    Change sheet names etc to suit.. add full filepath in A1.. example: C:\Users\apo\Desktop\csv\test.csv

    Results start in A2 down...

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    03-07-2014
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Editing a recorded macro of a csv import error

    I tried testing it and it gives me a subscript out of range error.

  5. #5
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

    Re: Editing a recorded macro of a csv import error

    Check the Sheet names are correct in all places and file path is correct..

  6. #6
    Registered User
    Join Date
    03-07-2014
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Editing a recorded macro of a csv import error

    They're correct. I made a new workbook and have the correct file path in A1.

  7. #7
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Editing a recorded macro of a csv import error

    Welcome to the forum.

    We'd like to continue to help you but first..

    Pls take some minutes to read forum rules and specially-in this case- rule#3 and add code tags around your code.
    Then anyone will be able to help you.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

+ 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. Editing my recorded macro to get it to work despite a range length change
    By dasseya1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-15-2013, 11:15 AM
  2. [SOLVED] Editing a recorded macro to be useable on future data sets
    By Jason210 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-25-2012, 01:09 PM
  3. Generalizing a recorded macro to import daily text file
    By excelVBAn00b in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-21-2012, 05:36 PM
  4. Excel macro, editing recorded cell.. not current
    By TroyT in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-12-2006, 09:30 AM
  5. Editing recorded macro code. Referencing workbook name in functio
    By Mark in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-06-2005, 07: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