ADO & data manipulation

  • I am using ADO to copy a range (10 rows by 1 column) from all files in a specified folder into a worksheet in the active workbook. My understanding of ADO is a little hazy, I copied some code from a website I found and have managed to tweak it a little as I want the data in the active workbook to be transposed, i.e 1 row by 10 columns. This works but there is a niggle in that the last file's data is copied across both as 10x1 and 1x10. I can quite easily manually delete the nine superfluous cells, but could somebody here kindly explain to me why it does this?


    VBA:


    This is the GetData function:


    I'm wondering if the problem is caused by this line:
    TargetRange.Cells(1, 1).copyFromRecordset rsData
    which automatically copies the data across in its original form, but I reckon this line is very important(!) and I don't know if there is an alternative syntax.

  • Re: ADO & data manipulation


    Bump!


    I wonder if anyone can find it in themselves to overlook my stupid mistake and help me on this one;) I've tried various things such as trying to put the data into an array, transpose and then put it back into the range, but nothing works. Also, if i try this with a range of more than column it doesn't work at all, I think because the transposing overwrites the original values. Does that make sense to anyone?

  • Re: ADO & data manipulation


    At a quick glance and certainly not tested :-


    Quote


    This works but there is a niggle in that the last file's data is copied across both as 10x1 and 1x10


    Probably the problem is here


    Code
    For r = 1 To 10 
                TargetRange.Cells(1, r) = TargetRange.Cells(r, 1) 
            Next r


    Try changing to


    Code
    For r = 1 To 10 
                TargetRange.Cells(1, r) = TargetRange.Cells(r, 1)
                 TargetRange.Cells(r, 1).value = ""
            Next r



    For the second part I think the whole code is setup for a single column. So a few things would need changing and you would have to decide how the second column would be transposed.


    HTH


    Carl

  • Re: ADO & data manipulation


    Thanks a lot for the suggestion Carl. It's not quite right, as it deletes the top two entries in column 1 (which I want), but I amended slightly and took out of the loop and that works. Thanks for putting me on the right track. Quite straightforward after all that.


    Code
    TargetRange.Range(Cells(2, 1), Cells(10, 1)).Value = ""


    For the multi-column I have made various amendments elsewhere, and am trying an adaptation of the function which includes this:


    Code
    TargetRange.Cells(1, 1).CopyFromRecordset rsData
            varray = TargetRange.Value
                ' Transpose the data
                For r = 1 To 13
                    For c = 1 To 20
                        TargetRange.Cells(c, r) = varray(r, c)
                    Next c
                Next r


    This works when I test in a simple example on a sheet (without the recordset reference) but in my actual procedure I get the error message and it doesn't transpose the data.


    Any thoughts?

  • Re: ADO & data manipulation


    The transpose code looks fine and I do not see any reason why the recordset should cause any issues. What error message do you get and in which line ?



    You will need to change the code in the main module to add more rows for each file.

  • Re: ADO & data manipulation


    carlmack, it's the SomethingWrong message:

    Code
    szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & sourceRange$ & "];"
    
    
        On Error GoTo SomethingWrongOn Error GoTo SomethingWrong
    
    
    SomethingWrong:
        MsgBox "The file name, Sheet name or Range is invalid of : " & SourceFile, _
               vbExclamation, "Error"
        On Error GoTo 0


    It loops through the procedure, copies the data to the sheet in its original layout, after which I get the error message. (I have amended the other script to allow for 20 rows.)

  • Re: ADO & data manipulation


    Do you know how to step through the code to see what is going on ?


    Take out the On Error Goto SomethingWrong statement and any other On Error statements. In the VBE add a breakpoint - click in the left column of the main code window next to the first statement in the module (If Range(sourceRange).Rows.Count = 1 Then ). Then use the play button or press F5. The code should stop at the breakpoint and then use F8 to advance line by line.


    Which line fails ?

  • Re: ADO & data manipulation


    Did you try

    Code
    TargetRange.Cells(c, r).value = varray(r, c).value


    I believe the value of a range defaults to the first row and first column cell value, so the answer to the second question is probably no.


    Can you post the whole code now because I am a bit lost where you are up to.

  • Re: ADO & data manipulation


    I don't blame you! The .Value makes no difference.



    The function:

  • Re: ADO & data manipulation


    I think the problem is that you are only sending over a single cell range as the target range.


    See


    Code
    Set destrange = sh.Cells(rnum + 1, 3)


    I believe you need to send the whole range where the recordset will be dropped.


    HTH

  • Re: ADO & data manipulation


    I still think the range is the problem.


    This works for me.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!