excel to access

[sedd_18]

Ok so here is what I have for my script:

strComputer = "."

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set colFiles = objWMIService.ExecQuery("SELECT * FROM CIM_DataFile WHERE Path = '\\test\\' AND Drive = 'C:' AND Extension = 'xls'")

set objConnection = CreateObject("ADODB.Connection")

objConnection.open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = 'c:\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], [type], [control number], [quantity] ) Values _

( '" & objExcel.Cells(3, 4).Value & "', '" & objExcel.Cells(6, 4).Value & "', '" _

& objExcel.Cells(3, 9).Value & "', '" & objExcel.Cells(6, 9).Value & "' );"

objconnection.execute sqlQuery

objExcel.quit

next

msgbox "finished"

I've rechecked the spelling and all the commas and what not and everything seems to be in place but it still stops at line 2. Could it be that my system is missing a runtime or somethign to be alble to execute this script? Thanks yet again for your patients and time.

[gunsmokingman]

Try to change the querry to this

This look for a folder called test so the drive letter does not matter.

Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
   Set colFiles = objWMIService.ExecQuery("SELECT * FROM CIM_DataFile WHERE Path = '\\test\\'AND Extension = 'xls'")

Or try it in this order

Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
   Set colFiles = objWMIService.ExecQuery("SELECT * FROM CIM_DataFile WHERE Drive = 'C:' And Path = '\\test\\'AND Extension = 'xls'")

[sedd_18]

I tried using both of the sections you suggested and continued to receive the same error. Could this error be the result of not having administrator privilages on the system? The only thing that I can think of is that I don't have permission to access the resource being called. Thanks for your time and patients.

[IcemanND]

with the exception of your sql statement which may be right depending upon how you formatted the 'control number' and 'quantity' fields in your database it works just fine for me as is.

If 'control number' and 'quantity' are number fields in your database and not test fields you need to remove the single quote from around their values in the sql query line.

My only thought is if there is something wrong with WMI, or some kind of restriction on WMI calls on the machine you are using. Try it on another system and see if it works.

Another thought, what are you using to write the script in? Notepad, wordpad, msword?

[sedd_18]

Ok so I couldn't find anything wrong with the script so I decided just to try retyping it. The second time I typed it up it worked. I don't know what I was doing wrong but its working now. I was using notepad to write the script. Thanks again for all your help and patients with me.

[IcemanND]

glad to hear it.

I have no idea I copied and pasted your post and it wrked when I changed my spreadsheets to match. some single quote must have been wrong or something.