Oops, my fault, I forgot to take that out.Originally Posted by scerchio
I don't know if I can help with this. I confess to not even looking at the code inside the For Each varFile In FoundFiles..Next varFile loop; I just wrote code to give you the same as Application.Filesearch gave. So I'm not sure what that code is doing.
I imagine that the lines that matter will be the likes of:
.Provider = "Microsoft.Jet.OLEDB.4.0"
and
.ConnectionString = "Data Source=" & varFile & ";Extended Properties=Excel 8.0;"
I suspect they'll need updating but I don't know what to, off the top of my head. Jet won't work in 64bit environments, you might be able to make it work with "Microsoft.ACE.OLEDB.12.0"
It looks like it might be adding records to an Access database?
Things that might help are knowing: what version of Excel you're using now, whether you're using 32-bit or 64-bit versions of Excel (and perhaps of Access), what version of Excel files you're interrogating, and maybe even your operating system bitness!
The code is only bringing in .xls files; now if you're updating perhaps you need to be looking at .xlsx, .xlsb and .xlsm files too? Perhaps strFileSpec = "20*.xls*" might take care of that part.
Another thing that might need looking at is the references in the VBE; go to the vb editor and at the top choose Tools, then References. Is anything marked a MISSING? Do you have a reference to Microsoft ActiveX DataObjects n.n Library?
How did you get the macro? Did you record your doing something in Excel and tweak it? If you still know the person who wrote the code, could you ask them to update this part?
You may find https://www.connectionstrings.com useful, but don't go ahead and install some driver or other, you've probably already got everything you need on your machine.
I think if you zip the files you'll be able to upload.
Reply With Quote