sedd_18 Posted June 15, 2006 Share Posted June 15, 2006 I tried searching the MS Office forums and in here and was unable to find what I am looking for. I have about 500 or so excel files that I want to take specfic cells out of and place into an access document. The excel documents are very structured and it would be the same exact cells out of every document. The only program I have available to me is MS Office. So the question I have is if there is a program or code or something I can use to complete this task. Any suggestions for sites and any code examples would be greatly appreciated. Thanks Link to comment Share on other sites More sharing options...
gunsmokingman Posted June 15, 2006 Share Posted June 15, 2006 I do not know if this will help you, as I do not use Excel or Access but there is a lot of script there that may help you out.The Hey, Scripting Guy! Archive: Microsoft Office Link to comment Share on other sites More sharing options...
IcemanND Posted June 16, 2006 Share Posted June 16, 2006 If it was me I'd write a vbscript that pulled the info from the spreadsheet and then connected to the access database via a dsnless connections and performed an append query. Link to comment Share on other sites More sharing options...
CoffeeFiend Posted June 16, 2006 Share Posted June 16, 2006 (edited) . Edited November 7, 2006 by crahak Link to comment Share on other sites More sharing options...
gunsmokingman Posted June 16, 2006 Share Posted June 16, 2006 So how long do you think it would take to do thisI have about 500 or so excel files that I want to take specfic cells out of and place into an access document.So what you are saying then is take each file import each one then save each new file. As to scripting if you where to look at most the script at The Hey, Scripting Guy! there is no comments, and beside it up to the person if they want to include comments in a script file. What would you call poor naming convention Fso = createobject("Scipting.FileSystemObject")ObjFso = createobject("Scipting.FileSystemObject")Act = createobject("Wscript.Shell")Act is a shorten for Action are these the poor name you speak of.Since there is no standard for naming things inside of scripts then it up to the individual what name they want to appear in there script. Like IcemanND said If it was me I'd write a vbscript that pulled the info from the spreadsheet and then connected to the access database via a dsnless connections and performed an append query.If the person has a basic understanding of VBS then it would take a hour or so to write the script then theycould reuse that script over and over again. Link to comment Share on other sites More sharing options...
sedd_18 Posted June 16, 2006 Author Share Posted June 16, 2006 A script would work pretty well. I have a very basic knowledge of VBS, I've been trying to teach myself how to write scripts but I haven't come across anything that would parse these documents and send them to an access db. I looked all through that link provided above, good stuff but I didn't see a topic that covered. I ran a search on google for "parse excel into access" and it came up with a bunch of crap for both excel and access but not the function I am looking for. I really appreciate you guys helping me out with this. Link to comment Share on other sites More sharing options...
IcemanND Posted June 16, 2006 Share Posted June 16, 2006 (edited) To get you started here's some code to insert data into a table named 'Main' in a Access database at 'c:\db1.mbd'. the myname.value, mydate.value in this case were being pulled from a web form, but are just the variables containing the data you wish to insert.if not isDate(mydate.value) then msgbox "Start Date is invalid", vbOKonly, "Invalid Date" exit subend ifset objConnection = CreateObject("ADODB.Connection")objConnection.open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = 'c:\db1.mdb'"sqlQuery = "INSERT INTO Main ( [name], [date], [address], [phone]) Values ( '" & myname.value & "', #" & mydate.value & "#, '" & myaddress.value & "', '" & myphone.value & "' );"objconnection.execute sqlQueryHow you get your data from the spreadsheet is going to depend upon how you spreadsheet is formatted.Here are three pages with good info for the excel side.http://www.microsoft.com/technet/scriptcen...05/hey0131.mspxhttp://www.microsoft.com/technet/scriptcen...05/hey1012.mspxhttp://www.microsoft.com/technet/scriptcen...06/hey0406.mspx Edited June 16, 2006 by IcemanND Link to comment Share on other sites More sharing options...
sedd_18 Posted June 17, 2006 Author Share Posted June 17, 2006 Ok so the second link provided above seems to be something that could help. I can use it to build an array of the data that I want to pull from every spreadsheet, and the sql snipet looks like it would be effective in wirting to the database. I have never worked with sql statements do I need any additional software to use them? So now my question is how do I get it to search thru a particular folder and pull the same data out of all the spreadsheets in it and insert a new record into the database for each spreadsheet? Thank you guys so much for helping with this. Link to comment Share on other sites More sharing options...
IcemanND Posted June 17, 2006 Share Posted June 17, 2006 everything you need is already on your machine for the sql stuff.Do you need to parse the files in a certain order or is any order ok? Yes it makes a big difference. Link to comment Share on other sites More sharing options...
sedd_18 Posted June 18, 2006 Author Share Posted June 18, 2006 Any order is ok, once I get it into the DB I have to generate a few reports from the data. So would it be easier to just declare the excel cells that I need as variables instead of placing them into an array? then using the varible names in the sql statement to insert them into the DB? or would building an array be the best option? Thanks yet again for your help. Link to comment Share on other sites More sharing options...
IcemanND Posted June 18, 2006 Share Posted June 18, 2006 strComputer = "."Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")Set colFiles = objWMIService.ExecQuery("SELECT * FROM CIM_DataFile WHERE Path = '\\Scripting\\test\\' AND Drive = 'C:' AND Extension = 'xls'")for each objFile in colFiles MSGBOX objFile.NamenextReplace the 'MSGBOX objFile.Name' with you code for getting the data from each of the spreadsheets and posting it to the database. You'll also need to replace \\scripting\test\\ with the path to your spreadsheets. Be sure to double all of the backslashes or it won't work.As far as how you hold the data it doesn't really matter. You could even use the direct references to the cells in the sql statement if desired.Another option I just thought of is that you could just link the access database to the spreadsheets. Don't ask me how, I've never actually done it but my boss does it all the time. So it is possible. This way anytime a spreadsheet changed the database would change.b Link to comment Share on other sites More sharing options...
sedd_18 Posted June 19, 2006 Author Share Posted June 19, 2006 ok so here is what I have come up with first, I like the idea of calling specfic cells using the sql commands but I dont really know how to reference them. As far as linking the documtents there really is no need to do that cause each one is a report issued and they just wanna pull stats from them without having to go thru all of them. They know that I have a pretty good understanding of whats possible to do so they ask me all these questions. So here is what I have so far...strComputer = "."Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")Set colFiles = objWMIService.ExecQuery("SELECT * FROM CIM_DataFile WHERE Path = '\\Scripting\\test\\' AND Drive = 'C:' AND Extension = 'xls'")for each objFile in colFiles set objConnection = CreateObject("ADODB.Connection")objConnection.open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = 'c:\db1.mdb'"sqlQuery = "INSERT INTO Main ( [name], [date], [address], [phone]) Values ( '" & myname.value & "', #" & mydate.value & "#, '" & myaddress.value & "', '" & myphone.value & "' );"objconnection.execute sqlQuerynextso I have to change the path from '\\Scripting\\test\\' and Drive = 'C:' to where I have the script savedthen change the path for Data Source = 'c:\db1.mdb'" to the path to the dbthen on the sqlQuery = "INTSERT INTO Main ( [excel cells], ....) Values ( '" &mycell.value &"'..... );"I dont know a lot about using sql but it seems like a lot less code so I'm interested in learning how to use this method. So can I just reference the cells by name like this:then on the sqlQuery = "INTSERT INTO Main ( [C14], ....) Values ( '" &mycell.value &"'..... );"or is there more to it? Again I really appreciate all your help with this. I'm still in the early stages of learning this stuff and thanks for being patient with me. Link to comment Share on other sites More sharing options...
IcemanND Posted June 19, 2006 Share Posted June 19, 2006 (edited) so I have to change the path from '\\Scripting\\test\\' and Drive = 'C:' to where I have the script savedThis is the drive and path to the folder containing your spreadsheetsthen change the path for Data Source = 'c:\db1.mdb'" to the path to the dbYes, path and name of your databasethen on the sqlQuery = "INTSERT INTO Main ( [excel cells], ....) Values ( '" &mycell.value &"'..... );"Not exactly: INSERT INTO {Table Name in database to put data in} ({Table Field 1}, {Table Field 2}, ...}) Values ({Value for Field 1}, {Value for Field 2}, ...)I dont know a lot about using sql but it seems like a lot less code so I'm interested in learning how to use this method. So can I just reference the cells by name like this:then on the sqlQuery = "INTSERT INTO Main ( [C14], ....) Values ( '" &mycell.value &"'..... );"You'll still have to add code to get the values from each spreadsheet and then reference either those cells or associated variables in the Values section of the asql statement. or is there more to it? Again I really appreciate all your help with this. I'm still in the early stages of learning this stuff and thanks for being patient with me.Yes, more to it. Not a problem at least your making the attempt to understand it and do it unlike some postings of the past.This should get you values of cells in a spreadsheet:Set objExcel = CreateObject("Excel.Application")Set objWorkbook = objExcel.Workbooks.Open ("C:\Scripts\New_users.xls") Wscript.Echo "CN: " & objExcel.Cells(2, 1).Value Wscript.Echo "sAMAccountName: " & objExcel.Cells(2, 2).Value Wscript.Echo "GivenName: " & objExcel.Cells(4, 3).Value Wscript.Echo "LastName: " & objExcel.Cells(6, 4).ValueobjExcel.QuitNot Really realistic for the data being collected in this case but it should give you an idea. So the completed script would look something like this:strComputer = "."Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")Set colFiles = objWMIService.ExecQuery("SELECT * FROM CIM_DataFile WHERE Path = '\\scripting\\test\\' AND Drive = 'C:' AND Extension = 'xls'")set objConnection = CreateObject("ADODB.Connection")objConnection.open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = 'c:\scripting\test\test.mdb'"Set objExcel = CreateObject("Excel.Application")for each objFile in colFilesSet objWorkbook = objExcel.Workbooks.Open (objFile.Name)sqlQuery = "INSERT INTO Main ( [name], [address], [city], [state], [zip], [phone], [date]) Values _ ( '" & objExcel.Cells(2, 1).Value & "', '" & objExcel.Cells(2, 2).Value & "', '" _ & objExcel.Cells(2, 3).Value & "', '" & objExcel.Cells(2, 4).Value & "', " _ & objExcel.Cells(2, 5).Value & ", '" & objExcel.Cells(2, 6).Value & "', #" _ & objExcel.Cells(2, 7).Value & "# );"objconnection.execute sqlQueryobjExcel.quitnextmsgbox "finished"So you'll need to change the paths and drives, the database name, database table name, field names, and the cell references for your spreadsheets and you should be good to go. Note that in the sql statement the date field is a date/time value in my database so it is surrounded by pound signs (#) and the strings are surrounded by single quotes (') and numbers have no formatting delimiters (zip in my example). Edited June 19, 2006 by IcemanND Link to comment Share on other sites More sharing options...
sedd_18 Posted June 20, 2006 Author Share Posted June 20, 2006 Ok so I got the script written and all the paths set and cells named and what not but when I go to run it I get this wierd error message. It says:line 2 char 1 error 0x80041e code 8004100e source nullWhich is for this line:Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")After searching on the internet I found out that the code is for an Invalid Namespace error, the examples show were of misspellings in the code, but what about Set is misspelled? Should I declare the object as a variable first? Again thank you for the help and patients with this. Link to comment Share on other sites More sharing options...
IcemanND Posted June 20, 2006 Share Posted June 20, 2006 Nothing is misspelled that I see. Post your entire modified code adn we can make sure there isn't something else wierd going on. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now