Excel - Import sheet from other workbook
up vote
0
down vote
favorite
I'm stalled, and need some help.
The function i need is to open workbooks through a dialogbox and import a specific sheet into the active workbook.
The below macro work if there is only one sheet, but someone got the idea to add another sheet and then it fails...
How do I change the code so it's only imports a specific named sheet?
<pre>
<code>
Sub Files()
Dim openfiles
Dim wb As Workbook
Dim sourcewb As Workbook
Dim newName As String
Dim x As Integer
Set wb = Application.ActiveWorkbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
openfiles = Application.GetOpenFilename(FileFilter:="Microsoft Excel Files
(*.xls;*.xlsx),*.xls;*.xlsx", MultiSelect:=True, Title:="Select file(s) for
import!")
If TypeName(openfiles) = "Boolean" Then
MsgBox "You have to choose a file"
GoTo ExitHandler
End If
With wb
x = 1
While x <= UBound(openfiles)
Set sourcewb = Workbooks.Open(Filename:=openfiles(x))
newName = sourcewb.Name
For i = 1 To sourcewb.Sheets.Count
sourcewb.Worksheets(i).Copy After:=.Sheets(.Sheets.Count)
.Worksheets(.Sheets.Count).Name = newName
Next
sourcewb.Close
x = x + 1
Wend
End With
'There is a lot of other code below this
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
</code>
</pre>
excel vba import
asked Nov 21 at 16:30
and
1
add a comment |
up vote
0
down vote
favorite
I'm stalled, and need some help.
The function i need is to open workbooks through a dialogbox and import a specific sheet into the active workbook.
The below macro work if there is only one sheet, but someone got the idea to add another sheet and then it fails...
How do I change the code so it's only imports a specific named sheet?
<pre>
<code>
Sub Files()
Dim openfiles
Dim wb As Workbook
Dim sourcewb As Workbook
Dim newName As String
Dim x As Integer
Set wb = Application.ActiveWorkbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
openfiles = Application.GetOpenFilename(FileFilter:="Microsoft Excel Files
(*.xls;*.xlsx),*.xls;*.xlsx", MultiSelect:=True, Title:="Select file(s) for
import!")
If TypeName(openfiles) = "Boolean" Then
MsgBox "You have to choose a file"
GoTo ExitHandler
End If
With wb
x = 1
While x <= UBound(openfiles)
Set sourcewb = Workbooks.Open(Filename:=openfiles(x))
newName = sourcewb.Name
For i = 1 To sourcewb.Sheets.Count
sourcewb.Worksheets(i).Copy After:=.Sheets(.Sheets.Count)
.Worksheets(.Sheets.Count).Name = newName
Next
sourcewb.Close
x = x + 1
Wend
End With
'There is a lot of other code below this
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
</code>
</pre>
excel vba import
asked Nov 21 at 16:30
and
1
When you say "it fails" are you getting an error? Which line of code is producing an error? Or is it simply not doing what it is supposed to do?
– ArcherBird
Nov 21 at 16:49
Welcome to SO. Could not understand question clearly. Tried your code with minor modification for unique worksheet name likenewName & iand it is importing all the sheets from the selected files. However if you mean to avoid importing empty sheet may use the link to detect empty sheets.
– Ahmed AU
Nov 21 at 16:55
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I'm stalled, and need some help.
The function i need is to open workbooks through a dialogbox and import a specific sheet into the active workbook.
The below macro work if there is only one sheet, but someone got the idea to add another sheet and then it fails...
How do I change the code so it's only imports a specific named sheet?
<pre>
<code>
Sub Files()
Dim openfiles
Dim wb As Workbook
Dim sourcewb As Workbook
Dim newName As String
Dim x As Integer
Set wb = Application.ActiveWorkbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
openfiles = Application.GetOpenFilename(FileFilter:="Microsoft Excel Files
(*.xls;*.xlsx),*.xls;*.xlsx", MultiSelect:=True, Title:="Select file(s) for
import!")
If TypeName(openfiles) = "Boolean" Then
MsgBox "You have to choose a file"
GoTo ExitHandler
End If
With wb
x = 1
While x <= UBound(openfiles)
Set sourcewb = Workbooks.Open(Filename:=openfiles(x))
newName = sourcewb.Name
For i = 1 To sourcewb.Sheets.Count
sourcewb.Worksheets(i).Copy After:=.Sheets(.Sheets.Count)
.Worksheets(.Sheets.Count).Name = newName
Next
sourcewb.Close
x = x + 1
Wend
End With
'There is a lot of other code below this
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
</code>
</pre>
excel vba import
asked Nov 21 at 16:30
and
1
I'm stalled, and need some help.
The function i need is to open workbooks through a dialogbox and import a specific sheet into the active workbook.
The below macro work if there is only one sheet, but someone got the idea to add another sheet and then it fails...
How do I change the code so it's only imports a specific named sheet?
<pre>
<code>
Sub Files()
Dim openfiles
Dim wb As Workbook
Dim sourcewb As Workbook
Dim newName As String
Dim x As Integer
Set wb = Application.ActiveWorkbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
openfiles = Application.GetOpenFilename(FileFilter:="Microsoft Excel Files
(*.xls;*.xlsx),*.xls;*.xlsx", MultiSelect:=True, Title:="Select file(s) for
import!")
If TypeName(openfiles) = "Boolean" Then
MsgBox "You have to choose a file"
GoTo ExitHandler
End If
With wb
x = 1
While x <= UBound(openfiles)
Set sourcewb = Workbooks.Open(Filename:=openfiles(x))
newName = sourcewb.Name
For i = 1 To sourcewb.Sheets.Count
sourcewb.Worksheets(i).Copy After:=.Sheets(.Sheets.Count)
.Worksheets(.Sheets.Count).Name = newName
Next
sourcewb.Close
x = x + 1
Wend
End With
'There is a lot of other code below this
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
</code>
</pre>
excel vba import
excel vba import
asked Nov 21 at 16:30
and
1
asked Nov 21 at 16:30
and
1
asked Nov 21 at 16:30
and
1
asked Nov 21 at 16:30
and
1
asked Nov 21 at 16:30
and
1
1
When you say "it fails" are you getting an error? Which line of code is producing an error? Or is it simply not doing what it is supposed to do?
– ArcherBird
Nov 21 at 16:49
Welcome to SO. Could not understand question clearly. Tried your code with minor modification for unique worksheet name likenewName & iand it is importing all the sheets from the selected files. However if you mean to avoid importing empty sheet may use the link to detect empty sheets.
– Ahmed AU
Nov 21 at 16:55
add a comment |
When you say "it fails" are you getting an error? Which line of code is producing an error? Or is it simply not doing what it is supposed to do?
– ArcherBird
Nov 21 at 16:49
Welcome to SO. Could not understand question clearly. Tried your code with minor modification for unique worksheet name likenewName & iand it is importing all the sheets from the selected files. However if you mean to avoid importing empty sheet may use the link to detect empty sheets.
– Ahmed AU
Nov 21 at 16:55
When you say "it fails" are you getting an error? Which line of code is producing an error? Or is it simply not doing what it is supposed to do?
– ArcherBird
Nov 21 at 16:49
When you say "it fails" are you getting an error? Which line of code is producing an error? Or is it simply not doing what it is supposed to do?
– ArcherBird
Nov 21 at 16:49
Welcome to SO. Could not understand question clearly. Tried your code with minor modification for unique worksheet name like
newName & i and it is importing all the sheets from the selected files. However if you mean to avoid importing empty sheet may use the link to detect empty sheets.– Ahmed AU
Nov 21 at 16:55
Welcome to SO. Could not understand question clearly. Tried your code with minor modification for unique worksheet name like
newName & i and it is importing all the sheets from the selected files. However if you mean to avoid importing empty sheet may use the link to detect empty sheets.– Ahmed AU
Nov 21 at 16:55
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
How do I change the code so it's only imports a specific named sheet?
Your code is not testing for sheet names, it just copies all sheets from the sourcewb workbook to the destination one. If you want to copy only sheets with a specified name, you have to check if it exists:
For i = 1 To sourcewb.Sheets.Count
If sourcewb.Worksheets(i).Name = "This is it" Then
sourcewb.Worksheets(i).Copy After:=.Sheets(.Sheets.Count)
.Worksheets(.Sheets.Count).Name = newName
sourcewb.Close
Exit Sub
End If
x = x + 1
Next
answered Nov 21 at 22:28
Michal Rosa
1,219614
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',
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%2f53416555%2fexcel-import-sheet-from-other-workbook%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
up vote
0
down vote
How do I change the code so it's only imports a specific named sheet?
Your code is not testing for sheet names, it just copies all sheets from the sourcewb workbook to the destination one. If you want to copy only sheets with a specified name, you have to check if it exists:
For i = 1 To sourcewb.Sheets.Count
If sourcewb.Worksheets(i).Name = "This is it" Then
sourcewb.Worksheets(i).Copy After:=.Sheets(.Sheets.Count)
.Worksheets(.Sheets.Count).Name = newName
sourcewb.Close
Exit Sub
End If
x = x + 1
Next
answered Nov 21 at 22:28
Michal Rosa
1,219614
add a comment |
up vote
0
down vote
How do I change the code so it's only imports a specific named sheet?
Your code is not testing for sheet names, it just copies all sheets from the sourcewb workbook to the destination one. If you want to copy only sheets with a specified name, you have to check if it exists:
For i = 1 To sourcewb.Sheets.Count
If sourcewb.Worksheets(i).Name = "This is it" Then
sourcewb.Worksheets(i).Copy After:=.Sheets(.Sheets.Count)
.Worksheets(.Sheets.Count).Name = newName
sourcewb.Close
Exit Sub
End If
x = x + 1
Next
answered Nov 21 at 22:28
Michal Rosa
1,219614
add a comment |
up vote
0
down vote
up vote
0
down vote
How do I change the code so it's only imports a specific named sheet?
Your code is not testing for sheet names, it just copies all sheets from the sourcewb workbook to the destination one. If you want to copy only sheets with a specified name, you have to check if it exists:
For i = 1 To sourcewb.Sheets.Count
If sourcewb.Worksheets(i).Name = "This is it" Then
sourcewb.Worksheets(i).Copy After:=.Sheets(.Sheets.Count)
.Worksheets(.Sheets.Count).Name = newName
sourcewb.Close
Exit Sub
End If
x = x + 1
Next
answered Nov 21 at 22:28
Michal Rosa
1,219614
How do I change the code so it's only imports a specific named sheet?
Your code is not testing for sheet names, it just copies all sheets from the sourcewb workbook to the destination one. If you want to copy only sheets with a specified name, you have to check if it exists:
For i = 1 To sourcewb.Sheets.Count
If sourcewb.Worksheets(i).Name = "This is it" Then
sourcewb.Worksheets(i).Copy After:=.Sheets(.Sheets.Count)
.Worksheets(.Sheets.Count).Name = newName
sourcewb.Close
Exit Sub
End If
x = x + 1
Next
answered Nov 21 at 22:28
Michal Rosa
1,219614
answered Nov 21 at 22:28
Michal Rosa
1,219614
answered Nov 21 at 22:28
Michal Rosa
1,219614
answered Nov 21 at 22:28
Michal Rosa
1,219614
1,219614
add a comment |
add a comment |
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53416555%2fexcel-import-sheet-from-other-workbook%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
When you say "it fails" are you getting an error? Which line of code is producing an error? Or is it simply not doing what it is supposed to do?
– ArcherBird
Nov 21 at 16:49
Welcome to SO. Could not understand question clearly. Tried your code with minor modification for unique worksheet name like
newName & iand it is importing all the sheets from the selected files. However if you mean to avoid importing empty sheet may use the link to detect empty sheets.– Ahmed AU
Nov 21 at 16:55