PDA

View Full Version : Directory Issue



GribbiN
05-24-2015, 03:01 PM
Hi, Forum looks great!
Hopefully someone can help with what is probably a very basic question

I inherited a workbook from a team member who no longer works with me and there are some codes that don't work on my setup.
When debugging it appears the directory used at creation differs from mine
This is my location
C:\Users\jgribbin\Desktop\Walmart ASDA\ScannedDocs
I can live without the store creation folder but i would like the check store files function to work and to search in the above directory

My knowledge is limited and until work gets me on the courses i'm pretty much a copy and paste coder. I can uplaod the orginal work book and/or my own tweaked version.

Thanks in advance for any tips or help received

John



13493


Sub createstorefolder()

Dim strFolder As String






MkDir Range("storefolder") & Range("nameofstore")


MsgBox ("Store Folder Created")


End Sub


Sub checkstorefiles()


Range("A16").Select

Do While ActiveCell.Value <> ""


DirFile = Range("storefolder") & Range("nameofstore") & "\" & ActiveCell.Value & ".pdf"
If Dir(DirFile) = "" Then
ActiveCell.Offset(0, 1).Value = "N"
Else
ActiveCell.Offset(0, 1).Value = "Y"
End If


ActiveCell.Offset(1, 0).Select


Loop




End Sub

Kenneth Hobs
05-24-2015, 05:20 PM
Welcome to the forum!

To build the path to your desktop folder:

CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator

GribbiN
05-24-2015, 10:10 PM
Welcome to the forum!

To build the path to your desktop folder:

CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator

Thanks for the welcome.

i get

compile error, invalid outside procedure

the word desktop highlights

mancubus
05-24-2015, 11:44 PM
welcome to VBAX gribbin.

did you copy the code Kenneth posted inside a procedure? (Between sub and end sub?)

https://msdn.microsoft.com/en-us/library/office/gg264268.aspx

GribbiN
05-25-2015, 12:16 AM
Thanks mancubus
13494

mancubus
05-25-2015, 01:21 AM
what does this line return



Sub Test
MsgBox CreateObject("WScript.Shell").SpecialFolders("Desktop")
End Sub


and is it the same as value in range storefolder?

if so, does value in range storefolder end with a \ (backslash)?

this all i can say without seeing the workbook...

GribbiN
05-25-2015, 01:32 AM
13498
Thanks for helping. I have attached my workbook

13497

mancubus
05-25-2015, 02:12 AM
welcome.


so they are not the same.

afaik, starting from Windows Vista, the default location for special folders such as desktop is Users. i think the workbook you posted is created in Windows XP since Documents and Settings is used.

the value is C:\Documents and Settings\jgribbin\Desktop\ASDA Stores\
what you need is C:\Users\jgribbin\Desktop\ASDA Stores\

try changing Documents and Settings to Users in R5.

GribbiN
05-25-2015, 02:23 AM
Perfect, Simple when you know how.

Thank you very much

mancubus
05-25-2015, 02:51 AM
you are welcome.

if the new folders and files will be on your desktop i would use something like this.



Sub createstorefolder()
Dim strFolder As String

strFolder = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & "ASDA Stores\" & Range("nameofstore")

MkDir strFolder

MsgBox ("Store Folder Created")
End Sub