Specify Path in Excel VBA when running Macro
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:ObjektKundenerklärungOSRAMConverter"
' *** If folder is empty/full message ***``
Dim fs, strFolderPath, oFolder
Set fs = CreateObject("Scripting.FileSystemObject")
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
strFolderPath = "Q:ObjektKundenerklärungOSRAMConverter" ' *** 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
excel vba
edited Nov 21 '18 at 20:57
Cindy Meister
14.4k102134
asked Nov 21 '18 at 20:55
M1rzÄM1rzÄ
146
add a comment |
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:ObjektKundenerklärungOSRAMConverter"
' *** If folder is empty/full message ***``
Dim fs, strFolderPath, oFolder
Set fs = CreateObject("Scripting.FileSystemObject")
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
strFolderPath = "Q:ObjektKundenerklärungOSRAMConverter" ' *** 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
excel vba
edited Nov 21 '18 at 20:57
Cindy Meister
14.4k102134
asked Nov 21 '18 at 20:55
M1rzÄM1rzÄ
146
Possible duplicate of VBA - Folder Picker - set where to start
– Martin Zeitler
yesterday
add a comment |
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:ObjektKundenerklärungOSRAMConverter"
' *** If folder is empty/full message ***``
Dim fs, strFolderPath, oFolder
Set fs = CreateObject("Scripting.FileSystemObject")
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
strFolderPath = "Q:ObjektKundenerklärungOSRAMConverter" ' *** 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
excel vba
edited Nov 21 '18 at 20:57
Cindy Meister
14.4k102134
asked Nov 21 '18 at 20:55
M1rzÄM1rzÄ
146
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:ObjektKundenerklärungOSRAMConverter"
' *** If folder is empty/full message ***``
Dim fs, strFolderPath, oFolder
Set fs = CreateObject("Scripting.FileSystemObject")
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
strFolderPath = "Q:ObjektKundenerklärungOSRAMConverter" ' *** 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
excel vba
excel vba
edited Nov 21 '18 at 20:57
Cindy Meister
14.4k102134
asked Nov 21 '18 at 20:55
M1rzÄM1rzÄ
146
edited Nov 21 '18 at 20:57
Cindy Meister
14.4k102134
asked Nov 21 '18 at 20:55
M1rzÄM1rzÄ
146
edited Nov 21 '18 at 20:57
Cindy Meister
14.4k102134
edited Nov 21 '18 at 20:57
Cindy Meister
14.4k102134
edited Nov 21 '18 at 20:57
Cindy Meister
14.4k102134
14.4k102134
asked Nov 21 '18 at 20:55
M1rzÄM1rzÄ
146
asked Nov 21 '18 at 20:55
M1rzÄM1rzÄ
146
asked Nov 21 '18 at 20:55
M1rzÄM1rzÄ
146
146
Possible duplicate of VBA - Folder Picker - set where to start
– Martin Zeitler
yesterday
add a comment |
Possible duplicate of VBA - Folder Picker - set where to start
– Martin Zeitler
yesterday
Possible duplicate of VBA - Folder Picker - set where to start
– Martin Zeitler
yesterday
Possible duplicate of VBA - Folder Picker - set where to start
– Martin Zeitler
yesterday
add a comment |
1 Answer
1
active
oldest
votes
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 '18 at 20:59
extensionhelpextensionhelp
2527
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:ObjektKundenerklärungOSRAMConverter" 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....
– M1rzÄ
Nov 21 '18 at 21:49
You put in this value, and it generates an error?: Q:ObjektKundenerklärungOSRAMConverter
– extensionhelp
Nov 21 '18 at 21:50
Yes...i wrote you in edited
– M1rzÄ
Nov 21 '18 at 21:53
I don't know why you have variables HostFolder AND strFolderPath. Aren't they always the same value?
– extensionhelp
Nov 21 '18 at 21:54
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.
– M1rzÄ
Nov 21 '18 at 22:24
|
show 2 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53420352%2fspecify-path-in-excel-vba-when-running-macro%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 '18 at 20:59
extensionhelpextensionhelp
2527
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:ObjektKundenerklärungOSRAMConverter" 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....
– M1rzÄ
Nov 21 '18 at 21:49
You put in this value, and it generates an error?: Q:ObjektKundenerklärungOSRAMConverter
– extensionhelp
Nov 21 '18 at 21:50
Yes...i wrote you in edited
– M1rzÄ
Nov 21 '18 at 21:53
I don't know why you have variables HostFolder AND strFolderPath. Aren't they always the same value?
– extensionhelp
Nov 21 '18 at 21:54
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.
– M1rzÄ
Nov 21 '18 at 22:24
|
show 2 more comments
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 '18 at 20:59
extensionhelpextensionhelp
2527
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:ObjektKundenerklärungOSRAMConverter" 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....
– M1rzÄ
Nov 21 '18 at 21:49
You put in this value, and it generates an error?: Q:ObjektKundenerklärungOSRAMConverter
– extensionhelp
Nov 21 '18 at 21:50
Yes...i wrote you in edited
– M1rzÄ
Nov 21 '18 at 21:53
I don't know why you have variables HostFolder AND strFolderPath. Aren't they always the same value?
– extensionhelp
Nov 21 '18 at 21:54
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.
– M1rzÄ
Nov 21 '18 at 22:24
|
show 2 more comments
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 '18 at 20:59
extensionhelpextensionhelp
2527
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 '18 at 20:59
extensionhelpextensionhelp
2527
answered Nov 21 '18 at 20:59
extensionhelpextensionhelp
2527
answered Nov 21 '18 at 20:59
extensionhelpextensionhelp
2527
answered Nov 21 '18 at 20:59
extensionhelpextensionhelp
2527
2527
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:ObjektKundenerklärungOSRAMConverter" 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....
– M1rzÄ
Nov 21 '18 at 21:49
You put in this value, and it generates an error?: Q:ObjektKundenerklärungOSRAMConverter
– extensionhelp
Nov 21 '18 at 21:50
Yes...i wrote you in edited
– M1rzÄ
Nov 21 '18 at 21:53
I don't know why you have variables HostFolder AND strFolderPath. Aren't they always the same value?
– extensionhelp
Nov 21 '18 at 21:54
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.
– M1rzÄ
Nov 21 '18 at 22:24
|
show 2 more comments
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:ObjektKundenerklärungOSRAMConverter" 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....
– M1rzÄ
Nov 21 '18 at 21:49
You put in this value, and it generates an error?: Q:ObjektKundenerklärungOSRAMConverter
– extensionhelp
Nov 21 '18 at 21:50
Yes...i wrote you in edited
– M1rzÄ
Nov 21 '18 at 21:53
I don't know why you have variables HostFolder AND strFolderPath. Aren't they always the same value?
– extensionhelp
Nov 21 '18 at 21:54
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.
– M1rzÄ
Nov 21 '18 at 22:24
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:ObjektKundenerklärungOSRAMConverter" 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....
– M1rzÄ
Nov 21 '18 at 21:49
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:ObjektKundenerklärungOSRAMConverter" 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....
– M1rzÄ
Nov 21 '18 at 21:49
You put in this value, and it generates an error?: Q:ObjektKundenerklärungOSRAMConverter
– extensionhelp
Nov 21 '18 at 21:50
You put in this value, and it generates an error?: Q:ObjektKundenerklärungOSRAMConverter
– extensionhelp
Nov 21 '18 at 21:50
Yes...i wrote you in edited
– M1rzÄ
Nov 21 '18 at 21:53
Yes...i wrote you in edited
– M1rzÄ
Nov 21 '18 at 21:53
I don't know why you have variables HostFolder AND strFolderPath. Aren't they always the same value?
– extensionhelp
Nov 21 '18 at 21:54
I don't know why you have variables HostFolder AND strFolderPath. Aren't they always the same value?
– extensionhelp
Nov 21 '18 at 21:54
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.
– M1rzÄ
Nov 21 '18 at 22:24
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.
– M1rzÄ
Nov 21 '18 at 22:24
|
show 2 more comments
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53420352%2fspecify-path-in-excel-vba-when-running-macro%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Possible duplicate of VBA - Folder Picker - set where to start
– Martin Zeitler
yesterday