Full Version: Take one row from each sheet and move to "datasheet"?
UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel
Dashiel
Oct 23 2006, 10:36 AM
I am trying to take a non-functional Excel workbooks (actually a series of workbooks) and extract information for use in Access. I have taken a full row of cells and dedicated them on each worksheet to pulling all of the information I would like to move to access. What I need now, is a macro or VB script that will automatically pull the row of data from each sheet, and paste the values onto a seperate worksheet (titled datasheet). From there I can do a manual import into Access (until I figure out how to automate from there). Can someone help me to create a macro that will pull this row from each worksheet and paste the values into an unused row on the datasheet? Thanks in advance for the help!
Luceze
Oct 23 2006, 01:09 PM
A few questions.

Where are the workbooks stored?
Where is the range of cells you want to copy?
Dashiel
Oct 23 2006, 01:49 PM
Not sure what you mean by "where are the workbooks stored". They are project sheets housed on a corporate drive (public) if that helps. As for the range of cells, I have entered formulas to draw from various cells throughout the spreadsheet in the following range: I2:BZ2 . Everything I need from each sheet is now pulled over to this range via the (=) function. I think my biggest issue will be pulling this range and pasting Values so I don't end up with the dreaded #REF in each cell I try to automatically pull to the datasheet.
Luceze
Oct 23 2006, 01:55 PM
Why I asked that was to determine if the workbooks were stored in the same directory. Give me a minute and I will work something up for you.
Luceze
Oct 23 2006, 02:20 PM
See if this is close. This will loop through all files in a directory that you choose. If there are any files that you don't need, an adjustment will need to be made. I am unaware of your sheet names so I used the first sheet in each workbook. Also, the FileDialog object won't work with earlier versions of Excel. We can fix that if necessary.

CODE
[color="blue"]Sub[/color] CompileWBs()

    [color="blue"]Dim[/color] strFileName [color="blue"]As[/color] [color="blue"]String[/color], strPath [color="blue"]As[/color] [color="blue"]String[/color]

    [color="blue"]Dim[/color] fd [color="blue"]As[/color] FileDialog, vArr [color="blue"]As[/color] [color="blue"]Variant[/color]

    [color="blue"]Dim[/color] wb [color="blue"]As[/color] Workbook, wb2 [color="blue"]As[/color] Workbook

    [color="blue"]Set[/color] fd = Application.FileDialog(msoFileDialogFolderPicker)

        [color="blue"]With[/color] fd

            .AllowMultiSelect = [color="blue"]False[/color]

            .InitialFileName = "C:\"

            [color="blue"]If[/color] .Show <> -1 [color="blue"]Then[/color] [color="blue"]Exit[/color] [color="blue"]Sub[/color]

            strPath = .SelectedItems(1)

        [color="blue"]End[/color] [color="blue"]With[/color]

    [color="blue"]Set[/color] wb2 = Workbooks.Add

    

    strFileName = Dir(strPath & "\", vbNormal)

            [color="blue"]Do[/color] [color="blue"]While[/color] [color="blue"]CBool[/color](Len(strFileName))

                [color="blue"]Set[/color] wb = Workbooks.Open(strPath & "\" & strFileName)

                vArr = wb.Sheets(1).Range("I2:BZ2")

                wb2.Sheets(1).Range("A65536").End(xlUp)(2) _

                    .Resize(, [color="blue"]UBound[/color](vArr, 2)) = vArr

                wb.Close [color="blue"]False[/color]

                strFileName = Dir

            [color="blue"]Loop[/color]

            [color="blue"]Set[/color] wb = [color="blue"]Nothing[/color]

            [color="blue"]Set[/color] wb2 = [color="blue"]Nothing[/color]

[color="blue"]End[/color] [color="blue"]Sub[/color]


Edited by: Luceze on Mon Oct 23 15:46:09 EDT 2006.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.