tranform row to column...across 500 excel workbooks

i have a folder of 500 workbooks

i have data in a1
then across row 2 --- of varying length

i need to append all this data into one column

i would like to do it across every workbook automaticaly
finnstoneAsked:
Who is Participating?
 
aikimarkConnect With a Mentor Commented:
Here is a VBA routine to do this for all the csv files in a directory.  Change the cPath string literal to point to the directory with your 500 csv files.
Sub Q_28618360()
    Dim strFilename As String
    Dim wkb As Workbook
    Dim wks As Worksheet
    Const cPath As String = "C:\Users\AikiMark\Downloads\Q_28618360\"
    strFilename = Dir(cPath & "*.csv")
    Do Until Len(strFilename) = 0
        Set wkb = Workbooks.Open(cPath & strFilename)
        Set wks = wkb.Sheets(1)
        wks.Range(wks.Range("A2"), wks.Range("A2").Offset(ActiveSheet.Range("A2").End(xlToRight).Column - 1, 0)).Value = _
            WorksheetFunction.Transpose(wks.Range(wks.Range("A2"), wks.Range("A2").End(xlToRight)).Value)
        wks.Range(wks.Range("B2"), wks.Range("B2").End(xlToRight)).ClearContents
        wkb.Close True
        strFilename = Dir
    Loop

End Sub

Open in new window

0
 
aikimarkCommented:
Please post a workbook with representative data.
Where do you need to place this transformed data?
0
 
finnstoneAuthor Commented:
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
aikimarkCommented:
Your workbook needs to show what the transformed data should look like.  Please post your files in this thread.
animatedtabs.csv
0
 
finnstoneAuthor Commented:
I need All the data in column A that's it
0
 
aikimarkCommented:
0
 
finnstoneAuthor Commented:
yes
0
 
finnstoneAuthor Commented:
strFilename  is equaling "" everytime ...
0
 
aikimarkCommented:
What does your code look like?
Where are your CSV files?
0
 
finnstoneAuthor Commented:
i used your code exactly
put it in a module
0
 
finnstoneAuthor Commented:
yes i modified path to correct path
0
 
aikimarkCommented:
Are you invoking the routine in the module?
0
 
finnstoneAuthor Commented:
no. how do i do that?
0
 
aikimarkCommented:
just invoke it by name somewhere in the file, usually at the top of the file.

Also, make sure your path ends with a back slash, like mine did.
0
 
finnstoneAuthor Commented:
beautiful got it :)
0
 
finnstoneAuthor Commented:
thanks this is awesome!
0
 
finnstoneAuthor Commented:
great!!
0
 
aikimarkCommented:
Is this a one-time transform?
0
 
finnstoneAuthor Commented:
yeah already done. posting a new problem though :)
0
 
aikimarkCommented:
post a link to that new question in this thread
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.