Mixing
VBA Compile Error, Trying to copy sheet from external file to master file
0
I am getting a compile error on this code, I am trying to target SourceFileName without opening and load it into my active workbook sheet "data retrieval"
I am getting compile error, sub of function not defined.
Sub test()
Set appxl = CreateObject("Excel.application")
Dim myfile As Window
Dim currentSheet As Worksheet
Dim lastRow As Double
Dim sourceFileName As String
sourceFileName = "File name"
'Open Source File.xlsx
With appxl
.Workbooks.Open ActiveWorkbook.Path & "" & sourceFileName
.Visible = False
End With
'Get first sheet data
Set myfile = appxl.Windows(sourceFileName)
myfile.Activate
Set currentSheet = appxl.Sheets(12)
'Past the table in my current Excel file
lastRow = currentSheet.Range("A1").End(xlDown).Row
Sheets("Data retrieval").Range("A1:E" & lastRow) = currentSheet.Range("A1:E" & lastRow).Value
'Close Source File.xlsx
appxl.Workbooks(sourceFileName).Close
End Sub
After applying the below recommendation of Option Explicit and declaring appxl I am getting the following error:
excel vba excel-vba
asked Nov 21 '18 at 14:37
excelguyexcelguy
105121
add a comment |
0
I am getting a compile error on this code, I am trying to target SourceFileName without opening and load it into my active workbook sheet "data retrieval"
I am getting compile error, sub of function not defined.
Sub test()
Set appxl = CreateObject("Excel.application")
Dim myfile As Window
Dim currentSheet As Worksheet
Dim lastRow As Double
Dim sourceFileName As String
sourceFileName = "File name"
'Open Source File.xlsx
With appxl
.Workbooks.Open ActiveWorkbook.Path & "" & sourceFileName
.Visible = False
End With
'Get first sheet data
Set myfile = appxl.Windows(sourceFileName)
myfile.Activate
Set currentSheet = appxl.Sheets(12)
'Past the table in my current Excel file
lastRow = currentSheet.Range("A1").End(xlDown).Row
Sheets("Data retrieval").Range("A1:E" & lastRow) = currentSheet.Range("A1:E" & lastRow).Value
'Close Source File.xlsx
appxl.Workbooks(sourceFileName).Close
End Sub
After applying the below recommendation of Option Explicit and declaring appxl I am getting the following error:
excel vba excel-vba
asked Nov 21 '18 at 14:37
excelguyexcelguy
105121
In which line do you get the error?
– Pᴇʜ
Nov 21 '18 at 14:38
You need to declareappxl. This compiles on my end though
– urdearboy
Nov 21 '18 at 14:39
@Pᴇʜ very start.
– excelguy
Nov 21 '18 at 14:40
@excelguy you can be more specific here..... which line? The very start as inSub test()?
– urdearboy
Nov 21 '18 at 14:41
@urdearboy yes that line
– excelguy
Nov 21 '18 at 14:50
add a comment |
0
0
0
I am getting a compile error on this code, I am trying to target SourceFileName without opening and load it into my active workbook sheet "data retrieval"
I am getting compile error, sub of function not defined.
Sub test()
Set appxl = CreateObject("Excel.application")
Dim myfile As Window
Dim currentSheet As Worksheet
Dim lastRow As Double
Dim sourceFileName As String
sourceFileName = "File name"
'Open Source File.xlsx
With appxl
.Workbooks.Open ActiveWorkbook.Path & "" & sourceFileName
.Visible = False
End With
'Get first sheet data
Set myfile = appxl.Windows(sourceFileName)
myfile.Activate
Set currentSheet = appxl.Sheets(12)
'Past the table in my current Excel file
lastRow = currentSheet.Range("A1").End(xlDown).Row
Sheets("Data retrieval").Range("A1:E" & lastRow) = currentSheet.Range("A1:E" & lastRow).Value
'Close Source File.xlsx
appxl.Workbooks(sourceFileName).Close
End Sub
After applying the below recommendation of Option Explicit and declaring appxl I am getting the following error:
excel vba excel-vba
asked Nov 21 '18 at 14:37
excelguyexcelguy
105121
I am getting a compile error on this code, I am trying to target SourceFileName without opening and load it into my active workbook sheet "data retrieval"
I am getting compile error, sub of function not defined.
Sub test()
Set appxl = CreateObject("Excel.application")
Dim myfile As Window
Dim currentSheet As Worksheet
Dim lastRow As Double
Dim sourceFileName As String
sourceFileName = "File name"
'Open Source File.xlsx
With appxl
.Workbooks.Open ActiveWorkbook.Path & "" & sourceFileName
.Visible = False
End With
'Get first sheet data
Set myfile = appxl.Windows(sourceFileName)
myfile.Activate
Set currentSheet = appxl.Sheets(12)
'Past the table in my current Excel file
lastRow = currentSheet.Range("A1").End(xlDown).Row
Sheets("Data retrieval").Range("A1:E" & lastRow) = currentSheet.Range("A1:E" & lastRow).Value
'Close Source File.xlsx
appxl.Workbooks(sourceFileName).Close
End Sub
After applying the below recommendation of Option Explicit and declaring appxl I am getting the following error:
excel vba excel-vba
excel vba excel-vba
asked Nov 21 '18 at 14:37
excelguyexcelguy
105121
asked Nov 21 '18 at 14:37
excelguyexcelguy
105121
edited Nov 21 '18 at 14:55
excelguy
asked Nov 21 '18 at 14:37
excelguyexcelguy
105121
asked Nov 21 '18 at 14:37
excelguyexcelguy
105121
asked Nov 21 '18 at 14:37
excelguyexcelguy
105121
105121
In which line do you get the error?
– Pᴇʜ
Nov 21 '18 at 14:38
You need to declareappxl. This compiles on my end though
– urdearboy
Nov 21 '18 at 14:39
@Pᴇʜ very start.
– excelguy
Nov 21 '18 at 14:40
@excelguy you can be more specific here..... which line? The very start as inSub test()?
– urdearboy
Nov 21 '18 at 14:41
@urdearboy yes that line
– excelguy
Nov 21 '18 at 14:50
add a comment |
In which line do you get the error?
– Pᴇʜ
Nov 21 '18 at 14:38
You need to declareappxl. This compiles on my end though
– urdearboy
Nov 21 '18 at 14:39
@Pᴇʜ very start.
– excelguy
Nov 21 '18 at 14:40
@excelguy you can be more specific here..... which line? The very start as inSub test()?
– urdearboy
Nov 21 '18 at 14:41
@urdearboy yes that line
– excelguy
Nov 21 '18 at 14:50
In which line do you get the error?
– Pᴇʜ
Nov 21 '18 at 14:38
In which line do you get the error?
– Pᴇʜ
Nov 21 '18 at 14:38
You need to declare
appxl. This compiles on my end though– urdearboy
Nov 21 '18 at 14:39
You need to declare
appxl. This compiles on my end though– urdearboy
Nov 21 '18 at 14:39
@Pᴇʜ very start.
– excelguy
Nov 21 '18 at 14:40
@Pᴇʜ very start.
– excelguy
Nov 21 '18 at 14:40
@excelguy you can be more specific here..... which line? The very start as in
Sub test()?– urdearboy
Nov 21 '18 at 14:41
@excelguy you can be more specific here..... which line? The very start as in
Sub test()?– urdearboy
Nov 21 '18 at 14:41
@urdearboy yes that line
– excelguy
Nov 21 '18 at 14:50
@urdearboy yes that line
– excelguy
Nov 21 '18 at 14:50
add a comment |
1 Answer
1
active
oldest
votes
2
I recommend the following
Option Explicit
Sub test()
Dim AppXl As Excel.Application
Set AppXl = New Excel.Application
AppXl.Visible = False
Dim sourceFileName As String
sourceFileName = "File name"
'Open Source File.xlsx
Dim SourceWb As Workbook 'remember workbook in a variable so we can easily access it
Set SourceWb = AppXl.Workbooks.Open(ThisWorkbook.Path & "" & sourceFileName)
'Get first sheet data
Dim currentSheet As Worksheet
Set currentSheet = SourceWb.Sheets(12)
'Past the table in my current Excel file
Dim lastRow As Long 'row counting variables MUST be Long
lastRow = currentSheet.Range("A1").End(xlDown).Row
'define the workbook here
ThisWorkbook.Worksheets("Data retrieval").Range("A1:E" & lastRow).Value = currentSheet.Range("A1:E" & lastRow).Value
'Close Source File.xlsx
SourceWb.Close SaveChanges:=False 'close source without saving
AppXl.Quit
End Sub
answered Nov 21 '18 at 14:45
PᴇʜPᴇʜ
22.7k52850
Thanks,but now getting a new error. See edited question.
– excelguy
Nov 21 '18 at 14:55
@excelguy quit all instances of Excel (or restart your computer). And try again. Always provide in which line you get an error otherwise we cannot help.
– Pᴇʜ
Nov 21 '18 at 14:58
its kind of just repeating that error . I believe it was at ` Set SourceWb = AppXl.Workbooks.Open(ThisWorkbook.Path & "" & sourceFileName)` . Going to try restarting.
– excelguy
Nov 21 '18 at 14:59
What do you mean by "I believe"? You should be sure. The code line that throws the error is highlighted in yellow.
– Pᴇʜ
Nov 21 '18 at 15:02
1
Awesome, works.
– excelguy
Nov 21 '18 at 16:01
|
show 4 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
});
}
});
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%2f53414428%2fvba-compile-error-trying-to-copy-sheet-from-external-file-to-master-file%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
2
I recommend the following
Option Explicit
Sub test()
Dim AppXl As Excel.Application
Set AppXl = New Excel.Application
AppXl.Visible = False
Dim sourceFileName As String
sourceFileName = "File name"
'Open Source File.xlsx
Dim SourceWb As Workbook 'remember workbook in a variable so we can easily access it
Set SourceWb = AppXl.Workbooks.Open(ThisWorkbook.Path & "" & sourceFileName)
'Get first sheet data
Dim currentSheet As Worksheet
Set currentSheet = SourceWb.Sheets(12)
'Past the table in my current Excel file
Dim lastRow As Long 'row counting variables MUST be Long
lastRow = currentSheet.Range("A1").End(xlDown).Row
'define the workbook here
ThisWorkbook.Worksheets("Data retrieval").Range("A1:E" & lastRow).Value = currentSheet.Range("A1:E" & lastRow).Value
'Close Source File.xlsx
SourceWb.Close SaveChanges:=False 'close source without saving
AppXl.Quit
End Sub
answered Nov 21 '18 at 14:45
PᴇʜPᴇʜ
22.7k52850
Thanks,but now getting a new error. See edited question.
– excelguy
Nov 21 '18 at 14:55
@excelguy quit all instances of Excel (or restart your computer). And try again. Always provide in which line you get an error otherwise we cannot help.
– Pᴇʜ
Nov 21 '18 at 14:58
its kind of just repeating that error . I believe it was at ` Set SourceWb = AppXl.Workbooks.Open(ThisWorkbook.Path & "" & sourceFileName)` . Going to try restarting.
– excelguy
Nov 21 '18 at 14:59
What do you mean by "I believe"? You should be sure. The code line that throws the error is highlighted in yellow.
– Pᴇʜ
Nov 21 '18 at 15:02
1
Awesome, works.
– excelguy
Nov 21 '18 at 16:01
|
show 4 more comments
2
I recommend the following
Option Explicit
Sub test()
Dim AppXl As Excel.Application
Set AppXl = New Excel.Application
AppXl.Visible = False
Dim sourceFileName As String
sourceFileName = "File name"
'Open Source File.xlsx
Dim SourceWb As Workbook 'remember workbook in a variable so we can easily access it
Set SourceWb = AppXl.Workbooks.Open(ThisWorkbook.Path & "" & sourceFileName)
'Get first sheet data
Dim currentSheet As Worksheet
Set currentSheet = SourceWb.Sheets(12)
'Past the table in my current Excel file
Dim lastRow As Long 'row counting variables MUST be Long
lastRow = currentSheet.Range("A1").End(xlDown).Row
'define the workbook here
ThisWorkbook.Worksheets("Data retrieval").Range("A1:E" & lastRow).Value = currentSheet.Range("A1:E" & lastRow).Value
'Close Source File.xlsx
SourceWb.Close SaveChanges:=False 'close source without saving
AppXl.Quit
End Sub
answered Nov 21 '18 at 14:45
PᴇʜPᴇʜ
22.7k52850
Thanks,but now getting a new error. See edited question.
– excelguy
Nov 21 '18 at 14:55
@excelguy quit all instances of Excel (or restart your computer). And try again. Always provide in which line you get an error otherwise we cannot help.
– Pᴇʜ
Nov 21 '18 at 14:58
its kind of just repeating that error . I believe it was at ` Set SourceWb = AppXl.Workbooks.Open(ThisWorkbook.Path & "" & sourceFileName)` . Going to try restarting.
– excelguy
Nov 21 '18 at 14:59
What do you mean by "I believe"? You should be sure. The code line that throws the error is highlighted in yellow.
– Pᴇʜ
Nov 21 '18 at 15:02
1
Awesome, works.
– excelguy
Nov 21 '18 at 16:01
|
show 4 more comments
2
2
2
I recommend the following
Option Explicit
Sub test()
Dim AppXl As Excel.Application
Set AppXl = New Excel.Application
AppXl.Visible = False
Dim sourceFileName As String
sourceFileName = "File name"
'Open Source File.xlsx
Dim SourceWb As Workbook 'remember workbook in a variable so we can easily access it
Set SourceWb = AppXl.Workbooks.Open(ThisWorkbook.Path & "" & sourceFileName)
'Get first sheet data
Dim currentSheet As Worksheet
Set currentSheet = SourceWb.Sheets(12)
'Past the table in my current Excel file
Dim lastRow As Long 'row counting variables MUST be Long
lastRow = currentSheet.Range("A1").End(xlDown).Row
'define the workbook here
ThisWorkbook.Worksheets("Data retrieval").Range("A1:E" & lastRow).Value = currentSheet.Range("A1:E" & lastRow).Value
'Close Source File.xlsx
SourceWb.Close SaveChanges:=False 'close source without saving
AppXl.Quit
End Sub
answered Nov 21 '18 at 14:45
PᴇʜPᴇʜ
22.7k52850
I recommend the following
Option Explicit
Sub test()
Dim AppXl As Excel.Application
Set AppXl = New Excel.Application
AppXl.Visible = False
Dim sourceFileName As String
sourceFileName = "File name"
'Open Source File.xlsx
Dim SourceWb As Workbook 'remember workbook in a variable so we can easily access it
Set SourceWb = AppXl.Workbooks.Open(ThisWorkbook.Path & "" & sourceFileName)
'Get first sheet data
Dim currentSheet As Worksheet
Set currentSheet = SourceWb.Sheets(12)
'Past the table in my current Excel file
Dim lastRow As Long 'row counting variables MUST be Long
lastRow = currentSheet.Range("A1").End(xlDown).Row
'define the workbook here
ThisWorkbook.Worksheets("Data retrieval").Range("A1:E" & lastRow).Value = currentSheet.Range("A1:E" & lastRow).Value
'Close Source File.xlsx
SourceWb.Close SaveChanges:=False 'close source without saving
AppXl.Quit
End Sub
answered Nov 21 '18 at 14:45
PᴇʜPᴇʜ
22.7k52850
edited Nov 21 '18 at 14:50
answered Nov 21 '18 at 14:45
PᴇʜPᴇʜ
22.7k52850
answered Nov 21 '18 at 14:45
PᴇʜPᴇʜ
22.7k52850
answered Nov 21 '18 at 14:45
PᴇʜPᴇʜ
22.7k52850
22.7k52850
Thanks,but now getting a new error. See edited question.
– excelguy
Nov 21 '18 at 14:55
@excelguy quit all instances of Excel (or restart your computer). And try again. Always provide in which line you get an error otherwise we cannot help.
– Pᴇʜ
Nov 21 '18 at 14:58
its kind of just repeating that error . I believe it was at ` Set SourceWb = AppXl.Workbooks.Open(ThisWorkbook.Path & "" & sourceFileName)` . Going to try restarting.
– excelguy
Nov 21 '18 at 14:59
What do you mean by "I believe"? You should be sure. The code line that throws the error is highlighted in yellow.
– Pᴇʜ
Nov 21 '18 at 15:02
1
Awesome, works.
– excelguy
Nov 21 '18 at 16:01
|
show 4 more comments
Thanks,but now getting a new error. See edited question.
– excelguy
Nov 21 '18 at 14:55
@excelguy quit all instances of Excel (or restart your computer). And try again. Always provide in which line you get an error otherwise we cannot help.
– Pᴇʜ
Nov 21 '18 at 14:58
its kind of just repeating that error . I believe it was at ` Set SourceWb = AppXl.Workbooks.Open(ThisWorkbook.Path & "" & sourceFileName)` . Going to try restarting.
– excelguy
Nov 21 '18 at 14:59
What do you mean by "I believe"? You should be sure. The code line that throws the error is highlighted in yellow.
– Pᴇʜ
Nov 21 '18 at 15:02
1
Awesome, works.
– excelguy
Nov 21 '18 at 16:01
Thanks,but now getting a new error. See edited question.
– excelguy
Nov 21 '18 at 14:55
Thanks,but now getting a new error. See edited question.
– excelguy
Nov 21 '18 at 14:55
@excelguy quit all instances of Excel (or restart your computer). And try again. Always provide in which line you get an error otherwise we cannot help.
– Pᴇʜ
Nov 21 '18 at 14:58
@excelguy quit all instances of Excel (or restart your computer). And try again. Always provide in which line you get an error otherwise we cannot help.
– Pᴇʜ
Nov 21 '18 at 14:58
its kind of just repeating that error . I believe it was at ` Set SourceWb = AppXl.Workbooks.Open(ThisWorkbook.Path & "" & sourceFileName)` . Going to try restarting.
– excelguy
Nov 21 '18 at 14:59
its kind of just repeating that error . I believe it was at ` Set SourceWb = AppXl.Workbooks.Open(ThisWorkbook.Path & "" & sourceFileName)` . Going to try restarting.
– excelguy
Nov 21 '18 at 14:59
What do you mean by "I believe"? You should be sure. The code line that throws the error is highlighted in yellow.
– Pᴇʜ
Nov 21 '18 at 15:02
What do you mean by "I believe"? You should be sure. The code line that throws the error is highlighted in yellow.
– Pᴇʜ
Nov 21 '18 at 15:02
1
1
Awesome, works.
– excelguy
Nov 21 '18 at 16:01
Awesome, works.
– excelguy
Nov 21 '18 at 16:01
|
show 4 more comments
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%2f53414428%2fvba-compile-error-trying-to-copy-sheet-from-external-file-to-master-file%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
In which line do you get the error?
– Pᴇʜ
Nov 21 '18 at 14:38
You need to declare
appxl. This compiles on my end though– urdearboy
Nov 21 '18 at 14:39
@Pᴇʜ very start.
– excelguy
Nov 21 '18 at 14:40
@excelguy you can be more specific here..... which line? The very start as in
Sub test()?– urdearboy
Nov 21 '18 at 14:41
@urdearboy yes that line
– excelguy
Nov 21 '18 at 14:50