Welcome to my first post!

I have a bit of a problem and I hope you people can help!
I will, on a weekly basis, be in receipt of about 150 - 200 seperate Excel spreadsheets. All of the spreadsheets will only contain 2 rows of data (header row and 1 row of information).
What I need to do is import these spreadsheets into Access to retrieve the data from but I want to avoid the option of importing each individual spreadsheet one-by-one.
I have figured out the TransferSpreadsheet option in Access but that seems to only import 1 spreadsheet so that is why I am trying to merge all sheets into one.

Can anyone please offer me any advice on this predicement?

Cheers

Hello, perhaps something like the following:

Sub test()
[color="#00007F"]Dim i [color="#00007F"]As [color="#00007F"]Long
[color="#00007F"]With Application.FileSearch
    .NewSearch: .LookIn = "c:\temp\"
    .SearchSubFolders = [color="#00007F"]False
    .Filename = ".xls"
    [color="#00007F"]If .Execute() = 0 [color="#00007F"]Then [color="#00007F"]Exit [color="#00007F"]Sub
    [color="#00007F"]For i = 1 [color="#00007F"]To .FoundFiles.Count
        DoCmd.TransferSpreadsheet transfertype:=acImport, SpreadsheetType:=8, _
            tablename:="tmpTableName", Filename:=.FoundFiles(i), _
            Hasfieldnames:=False, Range:="Sheet1!B1:B11"
            [color="#007F00"]'Change "c:\temp\" to file path
            [color="#007F00"]'Change tmpTableName to Target Table
            [color="#007F00"]'Change Sheet1! to Sheet Name & !
            [color="#007F00"]'Change b1:b11 to the target range to import
    [color="#00007F"]Next
[color="#00007F"]End [color="#00007F"]With
[color="#00007F"]End [color="#00007F"]Sub

Where you import all worbooks from a directory, b1:b11 from Sheet1. The issue is that they all need to have the same sheet name for this approach. I have not tested this...

Thanks for the quick reply. I was kind of afraid that the only solution to this is to use VB or something.
I'm surprised that there is no easier way around this though. I guess that there is no simple solution using Excel or even the import function within Access, other than to import 150 individual tables.

Thanks again for the alternative.

Hello again Captain, how are Gopher and Doc? Permission to come aboard sir!

Had to do it...

200 imports, well, um, yes, I would far rather have VB do this than click a button 200 times. It's only one table, 200 hundo sheets. Should take a few minutes versus hours of mucking through a manual process. This isn't the alternative, it's the "a1" approach, as easy as it gets, a short procedure too.

You no like VB? I happen to think it's one of the major outstanding characteristics of Access and Excel myself.

I've only used VB for a few months. Had to get my head around a little bit of it to pass a module for my HND but haven't really used it since. I'll give your method a go and let you know my success.