By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,500 Members | 1,571 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,500 IT Pros & Developers. It's quick & easy.

Scanning directory of excel files

P: 3
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.
Mar 19 '08 #1
Share this Question
Share on Google+
7 Replies


100+
P: 121
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.
Mar 19 '08 #2

Expert 5K+
P: 8,434
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?
Mar 20 '08 #3

Expert 5K+
P: 8,434
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.
Mar 20 '08 #4

P: 3
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.

Expand|Select|Wrap|Line Numbers
  1. Sub test()
  2. Dim con As New ADODB.Connection
  3.  
  4. con.Open _
  5.    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\sampleimport2.mdb;Jet OLEDB:Engine Type=4"
  6.  
  7. Dim strSQL As String
  8. strSQL = "SELECT * INTO Table5 FROM [Sheet1$] IN ""C:\test.xls"" ""Excel 8.0; HDR=No;"""
  9.  
  10. con.Execute strSQL
  11.  
  12. con.Close
  13. Set con = Nothing
  14. End Sub
  15.  
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.
Mar 26 '08 #5

P: 3
Any tips? Thanks guys. :)
Mar 31 '08 #6

QVeen72
Expert 100+
P: 1,445
Hi,

Open one more Connection Object for Excel:

Expand|Select|Wrap|Line Numbers
  1. Dim ExConn As New ADODB.Connection
  2. With ExConn
  3.     .Provider = "Microsoft.Jet.OLEDB.4.0"
  4.     .Properties("Extended Properties").Value = "Excel 8.0"
  5.     .Open "C:\Book1.xls"
  6. End With
  7. Dim RST As New ADODB.RecordSet
  8. RST.Open "Select * From [Sheet1$] Where ColName='MY_Condition'",ExConn
  9. 'Or
  10. 'Sheet With Range:
  11. RST.Open "Select * from [Sheet1$A1:B20]", ExConn 
  12.  
Regards
Veena
Mar 31 '08 #7

Expert 5K+
P: 8,434
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.
Mar 31 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.