VBA to find the folder the workbook is saved in
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
I have a bit of code to pick out all xlsx files in a folder and place them into one workbook. I was wondering if I could get the vba to find the folder the master work book is saved in or if the vba could ask which folder I want to select.
Sub GetSheets()
Path = "S:xxxxx"
Filename = Dir(Path & "*.xlsx")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
Application.ScreenUpdating = True
End Sub
excel vba
asked Nov 23 '18 at 13:30
Phil ChamberlainPhil Chamberlain
206
add a comment |
0
I have a bit of code to pick out all xlsx files in a folder and place them into one workbook. I was wondering if I could get the vba to find the folder the master work book is saved in or if the vba could ask which folder I want to select.
Sub GetSheets()
Path = "S:xxxxx"
Filename = Dir(Path & "*.xlsx")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
Application.ScreenUpdating = True
End Sub
excel vba
asked Nov 23 '18 at 13:30
Phil ChamberlainPhil Chamberlain
206
If you create an xlxm workbook, create a module and add your code then amendPath = S:xxxxxto the required value, does this code do what you want? For example, do you mind the sequence of worksheets being reversed? Do you mind having to create a new xlxm workbook for each folder? If you do not mind, Bishonen_PL’s answer is probably correct if unclear.Path = ThisWorkbook.Pathsets Path to the name of the folder containing the workbook. When you tried this change, did you put the xlxm workbook in the folder containing the source workbooks?
– Tony Dallimore
Dec 4 '18 at 9:21
add a comment |
0
0
0
I have a bit of code to pick out all xlsx files in a folder and place them into one workbook. I was wondering if I could get the vba to find the folder the master work book is saved in or if the vba could ask which folder I want to select.
Sub GetSheets()
Path = "S:xxxxx"
Filename = Dir(Path & "*.xlsx")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
Application.ScreenUpdating = True
End Sub
excel vba
asked Nov 23 '18 at 13:30
Phil ChamberlainPhil Chamberlain
206
I have a bit of code to pick out all xlsx files in a folder and place them into one workbook. I was wondering if I could get the vba to find the folder the master work book is saved in or if the vba could ask which folder I want to select.
Sub GetSheets()
Path = "S:xxxxx"
Filename = Dir(Path & "*.xlsx")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
Application.ScreenUpdating = True
End Sub
excel vba
excel vba
asked Nov 23 '18 at 13:30
Phil ChamberlainPhil Chamberlain
206
asked Nov 23 '18 at 13:30
Phil ChamberlainPhil Chamberlain
206
asked Nov 23 '18 at 13:30
Phil ChamberlainPhil Chamberlain
206
asked Nov 23 '18 at 13:30
Phil ChamberlainPhil Chamberlain
206
asked Nov 23 '18 at 13:30
Phil ChamberlainPhil Chamberlain
206
206
If you create an xlxm workbook, create a module and add your code then amendPath = S:xxxxxto the required value, does this code do what you want? For example, do you mind the sequence of worksheets being reversed? Do you mind having to create a new xlxm workbook for each folder? If you do not mind, Bishonen_PL’s answer is probably correct if unclear.Path = ThisWorkbook.Pathsets Path to the name of the folder containing the workbook. When you tried this change, did you put the xlxm workbook in the folder containing the source workbooks?
– Tony Dallimore
Dec 4 '18 at 9:21
add a comment |
If you create an xlxm workbook, create a module and add your code then amendPath = S:xxxxxto the required value, does this code do what you want? For example, do you mind the sequence of worksheets being reversed? Do you mind having to create a new xlxm workbook for each folder? If you do not mind, Bishonen_PL’s answer is probably correct if unclear.Path = ThisWorkbook.Pathsets Path to the name of the folder containing the workbook. When you tried this change, did you put the xlxm workbook in the folder containing the source workbooks?
– Tony Dallimore
Dec 4 '18 at 9:21
If you create an xlxm workbook, create a module and add your code then amend
Path = S:xxxxx to the required value, does this code do what you want? For example, do you mind the sequence of worksheets being reversed? Do you mind having to create a new xlxm workbook for each folder? If you do not mind, Bishonen_PL’s answer is probably correct if unclear. Path = ThisWorkbook.Path sets Path to the name of the folder containing the workbook. When you tried this change, did you put the xlxm workbook in the folder containing the source workbooks?– Tony Dallimore
Dec 4 '18 at 9:21
If you create an xlxm workbook, create a module and add your code then amend
Path = S:xxxxx to the required value, does this code do what you want? For example, do you mind the sequence of worksheets being reversed? Do you mind having to create a new xlxm workbook for each folder? If you do not mind, Bishonen_PL’s answer is probably correct if unclear. Path = ThisWorkbook.Path sets Path to the name of the folder containing the workbook. When you tried this change, did you put the xlxm workbook in the folder containing the source workbooks?– Tony Dallimore
Dec 4 '18 at 9:21
add a comment |
2 Answers
2
active
oldest
votes
1
Path = ThisWorkbook.Path
should do the trick.
answered Nov 23 '18 at 13:42
Bishonen_PLBishonen_PL
13910
1
This answer was flagged as low-quality because of its length and content. Please add an explanation for your code.
– Jesse de Bruijne
Nov 23 '18 at 15:50
unfortunately I have tried this just now and it doesn't work it runs through but doesn't pick up any worksheets, the code I put does the same is you don't include the after the file path, is there anyway to add this?
– Phil Chamberlain
Nov 27 '18 at 16:28
add a comment |
1
You can refer This:
Sub openfldr()
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = Application.DefaultFilePath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Sub
answered Nov 23 '18 at 13:42
Manoj BabuManoj Babu
312
unfortunately I have tried this just now and it doesn't work it runs through but doesn't pick up any worksheets, the code I put does the same is you don't include the after the file path, is there anyway to add this?
– Phil Chamberlain
Nov 27 '18 at 16:28
add a comment |
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
});
}
});
draft saved
draft discarded
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%2f53447641%2fvba-to-find-the-folder-the-workbook-is-saved-in%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Path = ThisWorkbook.Path
should do the trick.
answered Nov 23 '18 at 13:42
Bishonen_PLBishonen_PL
13910
1
This answer was flagged as low-quality because of its length and content. Please add an explanation for your code.
– Jesse de Bruijne
Nov 23 '18 at 15:50
unfortunately I have tried this just now and it doesn't work it runs through but doesn't pick up any worksheets, the code I put does the same is you don't include the after the file path, is there anyway to add this?
– Phil Chamberlain
Nov 27 '18 at 16:28
add a comment |
1
Path = ThisWorkbook.Path
should do the trick.
answered Nov 23 '18 at 13:42
Bishonen_PLBishonen_PL
13910
1
This answer was flagged as low-quality because of its length and content. Please add an explanation for your code.
– Jesse de Bruijne
Nov 23 '18 at 15:50
unfortunately I have tried this just now and it doesn't work it runs through but doesn't pick up any worksheets, the code I put does the same is you don't include the after the file path, is there anyway to add this?
– Phil Chamberlain
Nov 27 '18 at 16:28
add a comment |
1
1
1
Path = ThisWorkbook.Path
should do the trick.
answered Nov 23 '18 at 13:42
Bishonen_PLBishonen_PL
13910
Path = ThisWorkbook.Path
should do the trick.
answered Nov 23 '18 at 13:42
Bishonen_PLBishonen_PL
13910
answered Nov 23 '18 at 13:42
Bishonen_PLBishonen_PL
13910
answered Nov 23 '18 at 13:42
Bishonen_PLBishonen_PL
13910
answered Nov 23 '18 at 13:42
Bishonen_PLBishonen_PL
13910
13910
1
This answer was flagged as low-quality because of its length and content. Please add an explanation for your code.
– Jesse de Bruijne
Nov 23 '18 at 15:50
unfortunately I have tried this just now and it doesn't work it runs through but doesn't pick up any worksheets, the code I put does the same is you don't include the after the file path, is there anyway to add this?
– Phil Chamberlain
Nov 27 '18 at 16:28
add a comment |
1
This answer was flagged as low-quality because of its length and content. Please add an explanation for your code.
– Jesse de Bruijne
Nov 23 '18 at 15:50
unfortunately I have tried this just now and it doesn't work it runs through but doesn't pick up any worksheets, the code I put does the same is you don't include the after the file path, is there anyway to add this?
– Phil Chamberlain
Nov 27 '18 at 16:28
1
1
This answer was flagged as low-quality because of its length and content. Please add an explanation for your code.
– Jesse de Bruijne
Nov 23 '18 at 15:50
This answer was flagged as low-quality because of its length and content. Please add an explanation for your code.
– Jesse de Bruijne
Nov 23 '18 at 15:50
unfortunately I have tried this just now and it doesn't work it runs through but doesn't pick up any worksheets, the code I put does the same is you don't include the after the file path, is there anyway to add this?
– Phil Chamberlain
Nov 27 '18 at 16:28
unfortunately I have tried this just now and it doesn't work it runs through but doesn't pick up any worksheets, the code I put does the same is you don't include the after the file path, is there anyway to add this?
– Phil Chamberlain
Nov 27 '18 at 16:28
add a comment |
1
You can refer This:
Sub openfldr()
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = Application.DefaultFilePath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Sub
answered Nov 23 '18 at 13:42
Manoj BabuManoj Babu
312
unfortunately I have tried this just now and it doesn't work it runs through but doesn't pick up any worksheets, the code I put does the same is you don't include the after the file path, is there anyway to add this?
– Phil Chamberlain
Nov 27 '18 at 16:28
add a comment |
1
You can refer This:
Sub openfldr()
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = Application.DefaultFilePath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Sub
answered Nov 23 '18 at 13:42
Manoj BabuManoj Babu
312
unfortunately I have tried this just now and it doesn't work it runs through but doesn't pick up any worksheets, the code I put does the same is you don't include the after the file path, is there anyway to add this?
– Phil Chamberlain
Nov 27 '18 at 16:28
add a comment |
1
1
1
You can refer This:
Sub openfldr()
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = Application.DefaultFilePath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Sub
answered Nov 23 '18 at 13:42
Manoj BabuManoj Babu
312
You can refer This:
Sub openfldr()
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = Application.DefaultFilePath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Sub
answered Nov 23 '18 at 13:42
Manoj BabuManoj Babu
312
answered Nov 23 '18 at 13:42
Manoj BabuManoj Babu
312
answered Nov 23 '18 at 13:42
Manoj BabuManoj Babu
312
answered Nov 23 '18 at 13:42
Manoj BabuManoj Babu
312
312
unfortunately I have tried this just now and it doesn't work it runs through but doesn't pick up any worksheets, the code I put does the same is you don't include the after the file path, is there anyway to add this?
– Phil Chamberlain
Nov 27 '18 at 16:28
add a comment |
unfortunately I have tried this just now and it doesn't work it runs through but doesn't pick up any worksheets, the code I put does the same is you don't include the after the file path, is there anyway to add this?
– Phil Chamberlain
Nov 27 '18 at 16:28
unfortunately I have tried this just now and it doesn't work it runs through but doesn't pick up any worksheets, the code I put does the same is you don't include the after the file path, is there anyway to add this?
– Phil Chamberlain
Nov 27 '18 at 16:28
unfortunately I have tried this just now and it doesn't work it runs through but doesn't pick up any worksheets, the code I put does the same is you don't include the after the file path, is there anyway to add this?
– Phil Chamberlain
Nov 27 '18 at 16:28
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53447641%2fvba-to-find-the-folder-the-workbook-is-saved-in%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
If you create an xlxm workbook, create a module and add your code then amend
Path = S:xxxxxto the required value, does this code do what you want? For example, do you mind the sequence of worksheets being reversed? Do you mind having to create a new xlxm workbook for each folder? If you do not mind, Bishonen_PL’s answer is probably correct if unclear.Path = ThisWorkbook.Pathsets Path to the name of the folder containing the workbook. When you tried this change, did you put the xlxm workbook in the folder containing the source workbooks?– Tony Dallimore
Dec 4 '18 at 9:21