Well, you have the file name 2 places in your code. The first of these is the Command string for the OLEDB, near the top; the second is the .SourceDataFile property setting near the bottom.
You would take the code I posted and put it first. Leave off the last 2 lines, since all you really want is the string for the file path + name.
Then, replace the 2 places in your code that have the file path + name and put the variable "strFile" there instead.
I would do it for you, but there are a couple of practical problems involved.
First, if I copy the code you posted and paste it into an Excel VBA Module, the entire connection string turns red. It seems, some bum carriage returns come along for the ride.
Second, even if that were not the case, all it takes is one bad key stroke on my part and the OLEDB call would fail. Since I cannot test it (but you can), it would be best for you to do it.
I can give you some tips, having done this a few times myself ... look closely at the long line (with multiple continuations) that starts with
That Connection string is an array. It may not be obvious at first, but if you parse it carefully, you will see that there are 2 members of the Array. The first member of the array is something like this:
And the second is:
What you want to do is re-write this with something like:
Get that working first (using an old file). Then, re-write the strCon variable, so it reads something like this:
Obviously, also replace
Once that is working, you can then use the code I posted to verify the folder (still) exists, and to read the file name from that folder.
Hope this helps.
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks