Using FileSystemObject in Access VBA

Question

I'm using Access 2010, and here is screenshot from my reference libraries and avalible FileSystemObject methods from within Access VB IDE:

enter image description here

When I initiate this object I can't seem to be able to access familiar methods, as I get exception with this example debug result:

enter image description here

I also used explicit declarations and initialized variables to get same results.

Any ideas what may be wrong?

To replay on @4dmonster comment, this is the actual array:

For Each p In Split("f:\temp\test\op1\gev_final_1.xlsx;f:\temp\test\op1\gev_final_2.xlsx;f:\temp\test\op1\gev_final_3.xlsx;f:\temp\test\op1\gev_final_4.xlsx;" & _
                     "f:\temp\test\op1\gev_final_5.xlsx;f:\temp\test\op1\gev_final_6.xlsx;f:\temp\test\op1\gev_final_7.xlsx;f:\temp\test\op2\gev_final_8.xlsx;" & _
                     "f:\temp\test\op2\gev_final_9.xlsx;f:\temp\test\op2\gev_final_10.xlsx;f:\temp\test\op2\gev_final_11.xlsx;f:\temp\test\op2\gev_final_12.xlsx;" & _
                     "f:\temp\test\op2\gev_final_13.xlsx;f:\temp\test\op3\gev_final_14.xlsx;f:\temp\test\op3\gev_final_15.xlsx;f:\temp\test\op3\gev_final_16.xlsx;" & _
                     "f:\temp\test\op3\gev_final_17.xlsx;f:\temp\test\op3\gev_final_18.xlsx;f:\temp\test\op3\gev_final_19.xlsx;f:\temp\test\op4\gev_final_20.xlsx;" & _
                     "f:\temp\test\op4\gev_final_21.xlsx;f:\temp\test\op4\gev_final_22.xlsx;f:\temp\test\op4\gev_final_23.xlsx;f:\temp\test\op4\gev_final_24.xlsx;" & _
                     "f:\temp\test\op5\gev_final_25.xlsx;f:\temp\test\op5\gev_final_26.xlsx;f:\temp\test\op5\gev_final_27.xlsx", ";")

It says "Path is not valid", although file exists. I now tested the code as VBS, and also WSH reports the same error. But if I print p variable and put the string in Windows run dialog file is opened as expected. The drive is USB, but that shouldn't matter... — theta
– theta, Commented Oct 20, 2013 at 16:29
I upadted the question with actual array, and will hopefully track this as I didn't expected same problem to arise with VBS. Thanks — theta
– theta, Commented Oct 20, 2013 at 17:14
Could it be that you are passing "f:\temp\test\op1\gev_final_1.xlsx" to GetFolder() and GetFolder() is complaining because there is no folder with that name? (Assuming that "f:\temp\test\op1\gev_final_1.xlsx" is actually a file....) — Gord Thompson
– Gord Thompson, Commented Oct 20, 2013 at 17:19

Gord Thompson · Accepted Answer · 2013-10-20 17:38:38Z

4

The problem here is that GetFolder() is being passed a string containing the full path to a file and it is rightfully complaining that such a folder does not exist. If you want to extract the folder in which a particular file resides then you could use something like

fso.GetFile("C:\Users\Public\Database1.accdb").ParentFolder

answered Oct 20, 2013 at 17:38

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using FileSystemObject in Access VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related