excel to access

[sedd_18]

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

[gunsmokingman]

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

[IcemanND]

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.

[CoffeeFiend]

.

Edited November 7, 2006 by crahak

[gunsmokingman]

So how long do you think it would take to do this

I 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 they

could reuse that script over and over again.

[sedd_18]

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.

[IcemanND]

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 sub
end if

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 sqlQuery

How 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.mspx

http://www.microsoft.com/technet/scriptcen...05/hey1012.mspx

http://www.microsoft.com/technet/scriptcen...06/hey0406.mspx

Edited June 16, 2006 by IcemanND

[sedd_18]

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.

[IcemanND]

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.

[sedd_18]

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.

[IcemanND]

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.Name
next

Replace 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

[sedd_18]

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 sqlQuery

next

so I have to change the path from '\\Scripting\\test\\' and Drive = 'C:' to where I have the script saved

then change the path for Data Source = 'c:\db1.mdb'" to the path to the db

then 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.

[IcemanND]

so I have to change the path from '\\Scripting\\test\\' and Drive = 'C:' to where I have the script saved

This is the drive and path to the folder containing your spreadsheets

then change the path for Data Source = 'c:\db1.mdb'" to the path to the db

Yes, path and name of your database

then 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).Value

objExcel.Quit

Not 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 colFiles

Set 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 sqlQuery

objExcel.quit
next

msgbox "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

[sedd_18]

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 null

Which 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.

[IcemanND]

Nothing is misspelled that I see. Post your entire modified code adn we can make sure there isn't something else wierd going on.