Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: Collate Data from csv files to excel sheet

  1. #11
    Junior Member
    Join Date
    Sep 2011
    Posts
    14
    Rep Power
    0
    I still see the the "Subscript out of range error". Can you recommend any settings i should do on my excel 2007 to get this working? Because, if it is working there and not in my machine, there might be difference somewhere. I am using windows7 and Excel2007.

    Cheers!

  2. #12
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi

    In which line the error occurs ?
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #13
    Junior Member
    Join Date
    Sep 2011
    Posts
    14
    Rep Power
    0
    Hi,

    Script stops at " k(n, j) = d(r, c) " and shows Subscript out of range error. As i see, the script is opening a .csv file randomly from the folder i specify, and the first cell(A1) of that worksheet is active. Also the first cell(A1) in excel sheet is active too. I can send you a recorded webex session if you would like to have a look.

    Thanks,
    Dhiraj

  4. #14
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi Dhiraj,

    Are you working on the real data or on the sample CSVs you posted here ?

    Anyway replace the For Next statement with the following.

    Code:
    For c = 1 To UBound(d, 2)
        If Len(Trim$(d(1, c))) Then
            j = dic.Item(Trim$(d(1, c)))
            On Error Resume Next
            k(n, j) = d(r, c)
            If Err.Number <> 0 Then
                MsgBox "Total Columns so far " & dic.Count & vbLf & _
                        "Counter :" & n & vbLf & _
                        "Current Col:" & j
                Err.Clear: On Error GoTo 0
                Exit Sub
            End If
        End If
    Next
    Let me know what the MsgBox says once it error out.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #15
    Junior Member
    Join Date
    Sep 2011
    Posts
    14
    Rep Power
    0
    Hi,

    Sorry for late reply. I see the MsgBox as "Total Columns so far 13, Counter : 0, Current Col: 1".
    Also i have tried the same script on my original data and also the data which is uploaded earlier. But no luck

    Thanks,
    -Dhiraj

  6. #16
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi,

    It works fine here. I'm not sure why it fails to work at your end. Anyway please find attached and try again.
    Attached Files Attached Files
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  7. #17
    Junior Member
    Join Date
    Sep 2011
    Posts
    14
    Rep Power
    0
    Wow.. This worked well. I can see the data is collated. But, if we observe the collated file, all the columns of files got collated well but the data is collated as one below the other.

    I guess, i will look look further into the script.

    Thank you.
    -Dhiraj

Similar Threads

  1. Macro To Close All CSV Files
    By Howardc in forum Excel Help
    Replies: 5
    Last Post: 03-15-2014, 05:24 PM
  2. export all worksheets to separate csv files
    By rabidfly in forum Excel Help
    Replies: 4
    Last Post: 11-18-2012, 02:11 PM
  3. Macro to copy data from a set of excel files
    By Sreejesh Menon in forum Excel Help
    Replies: 5
    Last Post: 11-15-2012, 11:17 AM
  4. Excel to Excel Data transfer without opening any of the files(source or target)
    By Transformer in forum Excel and VBA Tips and Tricks
    Replies: 14
    Last Post: 08-22-2012, 10:57 AM
  5. Macro to export sheet as CSV
    By Howardc in forum Excel Help
    Replies: 2
    Last Post: 07-25-2012, 08:59 PM

Tags for this Thread

Posting Permissions

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