Specify Path in Excel VBA when running Macro

Question

I am having and using this code for (part of it) for some transformations/filtering from certain files to certain files, plus some filtering on sheets and so on... Everything works good for me, but the colleagues from the work wanna also use this code on their PC*s, and there is a problem at start, it has to be edited in the code (path, location) so that someone else can use it - and I dont wanna that anyone from outside have an access to the code easily (ok theyre not skilled but still), so that can mess it up and make it crap what is possible.

Question, is there any line of code that gives like popup message simply asking to define from where you wanna take certain files to where? to avoid going into code and change from there? I hope I was clear enough...

Dim FileSystem As Object
Dim HostFolder As String

' *** Folder with systems to define (path) ***
 HostFolder = "Q:\Objekt\Kundenerklärung\OSRAM\Converter"

' *** If folder is empty/full message ***``
Dim fs, strFolderPath, oFolder
Set fs = CreateObject("Scripting.FileSystemObject")
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

strFolderPath = "Q:\Objekt\Kundenerklärung\OSRAM\Converter\" ' *** This is 
your folder to define ***
Set oFolder = fs.GetFolder(strFolderPath)
 If (oFolder.SubFolders.Count = 0) Then
 ' * Folder is Empty *
   MsgBox "Folder is empty!"
 Else
 ' * Folder isn't empty *
   MsgBox "Folder not empty. Subfolders count: " & oFolder.SubFolders.Count
 End If

 Set fs = Nothing

 Set FileSystem = CreateObject("Scripting.FileSystemObject")
 DoFolder FileSystem.GetFolder(HostFolder)

 Application.ScreenUpdating = True
 Application.Calculation = xlCalculationAutomatic

Possible duplicate of VBA - Folder Picker - set where to start — Martin Zeitler
– Martin Zeitler, Commented Jan 19, 2019 at 9:09

extensionhelp · Accepted Answer · 2018-11-21 20:59:01Z

1

Yes, you can ask for an input value as such:

strFolderPath = InputBox("What folder would you like to use?")

A more user-friendly solution would be to use a file dialog box, though it's somewhat more involved.

answered Nov 21, 2018 at 20:59

extensionhelp

5741 gold badge5 silver badges21 bronze badges

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

6 Comments

M1rzÄ Over a year ago

Well, it didnt work well...I mean it asks for path but then makes error in my line already, *** Folder with systems to define (path) *** HostFolder = "Q:\Objekt\Kundenerklärung\OSRAM\Converter" Is there posibility to completely change those locations on "input method"? So to ask from which path/folder should take files for convert and where to save after ? ...unfortunately I am not good in Macros/Vba stuff....

extensionhelp Over a year ago

You put in this value, and it generates an error?: Q:\Objekt\Kundenerklärung\OSRAM\Converter\

extensionhelp Over a year ago

I don't know why you have variables HostFolder AND strFolderPath. Aren't they always the same value?

M1rzÄ Over a year ago

I did lit ong ago-cant even remember, it was ment to be always in the same directory, without changing anything, but nowadays I have to change it, to make it simpler. BUt I tried to delete that line, gives me errors, they have to be both, otherwise cant open anything.

extensionhelp Over a year ago

What is this code meant to do? Just tell you whether a folder has subfolders or not? And if it does, tell you how many?

|

Collectives™ on Stack Overflow

Specify Path in Excel VBA when running Macro

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related