Excel VBA 2 inputs files each opend and copied with diffrent format instead of same
up vote
0
down vote
favorite
I have some reports downloaded from SAP as .xls (in same structure and format cells). I have macro which open each file a copy it to one "data" sheet. One file is copied correctly value "1" in cell with format "General" is copied and paste as "1" but values from other input files are opened, copied and paste as 10000000. I don't know why. Any suggestons? Thanks for your help.
Sub open_copy_data()
Workbooks.Open FileName:=macro_link & "" & file_name
Sheets(1).Copy After:=Workbooks(macro_name).Sheets(Workbooks(macro_name).Sheets.Count)
ActiveSheet.Name = Application.WorksheetFunction.Substitute(Left(file_name, 31), ".xlsx", "")
Workbooks(file_name).Close
End Sub
excel vba
edited Nov 8 at 16:09
Cindy Meister
13.2k101934
asked Nov 8 at 12:55
Witchery
64
add a comment |
up vote
0
down vote
favorite
I have some reports downloaded from SAP as .xls (in same structure and format cells). I have macro which open each file a copy it to one "data" sheet. One file is copied correctly value "1" in cell with format "General" is copied and paste as "1" but values from other input files are opened, copied and paste as 10000000. I don't know why. Any suggestons? Thanks for your help.
Sub open_copy_data()
Workbooks.Open FileName:=macro_link & "" & file_name
Sheets(1).Copy After:=Workbooks(macro_name).Sheets(Workbooks(macro_name).Sheets.Count)
ActiveSheet.Name = Application.WorksheetFunction.Substitute(Left(file_name, 31), ".xlsx", "")
Workbooks(file_name).Close
End Sub
excel vba
edited Nov 8 at 16:09
Cindy Meister
13.2k101934
asked Nov 8 at 12:55
Witchery
64
Open each SAP .xls file and check contents and format. If they are OK, then step through your code with the debugger and check the sheets in each step.
– Paul Ogilvie
Nov 8 at 13:53
I have found out that when all the files are open by duoble click, all values are corecct. When is the broken file is open throught file -> open, the wizard has determined that my data is Delimited. I tiried to add to my code: Format:=1 (and other options), but put all values to one cell and the second one has zero numbers only. How can I fix this?
– Witchery
Nov 8 at 15:04
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have some reports downloaded from SAP as .xls (in same structure and format cells). I have macro which open each file a copy it to one "data" sheet. One file is copied correctly value "1" in cell with format "General" is copied and paste as "1" but values from other input files are opened, copied and paste as 10000000. I don't know why. Any suggestons? Thanks for your help.
Sub open_copy_data()
Workbooks.Open FileName:=macro_link & "" & file_name
Sheets(1).Copy After:=Workbooks(macro_name).Sheets(Workbooks(macro_name).Sheets.Count)
ActiveSheet.Name = Application.WorksheetFunction.Substitute(Left(file_name, 31), ".xlsx", "")
Workbooks(file_name).Close
End Sub
excel vba
edited Nov 8 at 16:09
Cindy Meister
13.2k101934
asked Nov 8 at 12:55
Witchery
64
I have some reports downloaded from SAP as .xls (in same structure and format cells). I have macro which open each file a copy it to one "data" sheet. One file is copied correctly value "1" in cell with format "General" is copied and paste as "1" but values from other input files are opened, copied and paste as 10000000. I don't know why. Any suggestons? Thanks for your help.
Sub open_copy_data()
Workbooks.Open FileName:=macro_link & "" & file_name
Sheets(1).Copy After:=Workbooks(macro_name).Sheets(Workbooks(macro_name).Sheets.Count)
ActiveSheet.Name = Application.WorksheetFunction.Substitute(Left(file_name, 31), ".xlsx", "")
Workbooks(file_name).Close
End Sub
excel vba
excel vba
edited Nov 8 at 16:09
Cindy Meister
13.2k101934
asked Nov 8 at 12:55
Witchery
64
edited Nov 8 at 16:09
Cindy Meister
13.2k101934
asked Nov 8 at 12:55
Witchery
64
edited Nov 8 at 16:09
Cindy Meister
13.2k101934
edited Nov 8 at 16:09
Cindy Meister
13.2k101934
edited Nov 8 at 16:09
Cindy Meister
13.2k101934
13.2k101934
asked Nov 8 at 12:55
Witchery
64
asked Nov 8 at 12:55
Witchery
64
asked Nov 8 at 12:55
Witchery
64
64
Open each SAP .xls file and check contents and format. If they are OK, then step through your code with the debugger and check the sheets in each step.
– Paul Ogilvie
Nov 8 at 13:53
I have found out that when all the files are open by duoble click, all values are corecct. When is the broken file is open throught file -> open, the wizard has determined that my data is Delimited. I tiried to add to my code: Format:=1 (and other options), but put all values to one cell and the second one has zero numbers only. How can I fix this?
– Witchery
Nov 8 at 15:04
add a comment |
Open each SAP .xls file and check contents and format. If they are OK, then step through your code with the debugger and check the sheets in each step.
– Paul Ogilvie
Nov 8 at 13:53
I have found out that when all the files are open by duoble click, all values are corecct. When is the broken file is open throught file -> open, the wizard has determined that my data is Delimited. I tiried to add to my code: Format:=1 (and other options), but put all values to one cell and the second one has zero numbers only. How can I fix this?
– Witchery
Nov 8 at 15:04
Open each SAP .xls file and check contents and format. If they are OK, then step through your code with the debugger and check the sheets in each step.
– Paul Ogilvie
Nov 8 at 13:53
Open each SAP .xls file and check contents and format. If they are OK, then step through your code with the debugger and check the sheets in each step.
– Paul Ogilvie
Nov 8 at 13:53
I have found out that when all the files are open by duoble click, all values are corecct. When is the broken file is open throught file -> open, the wizard has determined that my data is Delimited. I tiried to add to my code: Format:=1 (and other options), but put all values to one cell and the second one has zero numbers only. How can I fix this?
– Witchery
Nov 8 at 15:04
I have found out that when all the files are open by duoble click, all values are corecct. When is the broken file is open throught file -> open, the wizard has determined that my data is Delimited. I tiried to add to my code: Format:=1 (and other options), but put all values to one cell and the second one has zero numbers only. How can I fix this?
– Witchery
Nov 8 at 15:04
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53208197%2fexcel-vba-2-inputs-files-each-opend-and-copied-with-diffrent-format-instead-of-s%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
Open each SAP .xls file and check contents and format. If they are OK, then step through your code with the debugger and check the sheets in each step.
– Paul Ogilvie
Nov 8 at 13:53
I have found out that when all the files are open by duoble click, all values are corecct. When is the broken file is open throught file -> open, the wizard has determined that my data is Delimited. I tiried to add to my code: Format:=1 (and other options), but put all values to one cell and the second one has zero numbers only. How can I fix this?
– Witchery
Nov 8 at 15:04