Scanning directory of excel files

[paul86]

Hi all, I have a vb question that i'm hoping I can get some help with.

I have a directory of excel files (probably about 1000) of them. They are quote sheets for the company I work for, and they are all the exact same format. I need to create some code in vb using microsoft access that will scan through this directory of excel files, and pull certain cells of information from each of them, and store this information into an access database.

Right now I can scan a single excel file into a database, but it scans the ENTIRE file, and like I said it's only a single file, I need to be able to scan a whole directory of them using a loop.

Anybody have any code that will help me out with this? Thanks a lot, appreciate any help.

[Sick0Fant]

Hi all, I have a vb question that i'm hoping I can get some help with.

I have a directory of excel files (probably about 1000) of them. They are quote sheets for the company I work for, and they are all the exact same format. I need to create some code in vb using microsoft access that will scan through this directory of excel files, and pull certain cells of information from each of them, and store this information into an access database.

Right now I can scan a single excel file into a database, but it scans the ENTIRE file, and like I said it's only a single file, I need to be able to scan a whole directory of them using a loop.

Anybody have any code that will help me out with this? Thanks a lot, appreciate any help.

I can tell you that it is possible to reference single cells. Workbooks have Worksheets and worksheets have cells.

As for opening all of the files... VB gives you a function to retrieve all the files from a directory.

[Killer42]

When you say you can "scan" a single file into a database, I guess you must be referring to the import function.

I think what you will need to do in this case is use something like the FileSystemObject to scan through all the "*.XLS" files in the directory, open each one in your VB application, extract the cell in question, and INSERT the value into your database.

We can help out, but I'd recommend you try to put together as much of it as possible for yourself, as you'll understand it a lot better that way.

By the way, what versions of VB, Excel and Access do you have? Also, are you using standalone VB, or the (very similar) VBA macro language built into Access or Excel?

[Killer42]

Oh, one other thing. If you try the search box, you'll find there have been plenty of discussions here on working with both Excel worksheets and Access tables in VB. Though they're not usually used in combination, this shouldn't be any particular barrier.

[paul86]

Sorry, been really busy and haven't checked this thread in a little while, thanks for your responses though. I'm using the VB built into Access. I'm not using any sort of import function as of right now...this is the code i'm using right now to import an excel file into the access database.

Sub test()
Dim con As New ADODB.Connection
 
con.Open _
   "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\sampleimport2.mdb;Jet OLEDB:Engine Type=4"
 
Dim strSQL As String
strSQL = "SELECT * INTO Table5 FROM [Sheet1$] IN ""C:\test.xls"" ""Excel 8.0; HDR=No;"""
 
con.Execute strSQL
 
con.Close
Set con = Nothing
End Sub
 

Like I said, this code works fine, but there are a couple problems.

1) It only imports the ONE file, I need to be able to scan through a directory and import info from lots of them.

2) It's importing the entire excel file. I need to be able to just pick certain cells of text to import, not the entire file. I've been fiddling around with it but just can't seem to get anything to work.

If you have any suggestions it would be greatly appreciated. Thanks guys.

[paul86]

Any tips? Thanks guys. :)

[QVeen72]

Hi,

Open one more Connection Object for Excel:

Dim ExConn As New ADODB.Connection
With ExConn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Extended Properties").Value = "Excel 8.0"
    .Open "C:\Book1.xls"
End With
Dim RST As New ADODB.RecordSet
RST.Open "Select * From [Sheet1$] Where ColName='MY_Condition'",ExConn
'Or
'Sheet With Range:
RST.Open "Select * from [Sheet1$A1:B20]", ExConn 
 

Regards
Veena

[Killer42]

Interesting...

That looks like the sort of information that should go in a HowTo, Veena. It's the kind of thing that I never even guessed there was anything to know about.