Question description:
This user has given permission to use the problem statement for this
blog.
Hello --
I do get an error message when trying to auto populate data from a different workbook in excel-- through Data >>> Get Data >>> from file >>> from workbook. The column indicates numbers of more than 20 digits.
Solved by G. W. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
03/07/2018 - 06:01
hi
User
03/07/2018 - 06:01
I use excel daily
Excelchat Expert
03/07/2018 - 06:01
Welcome, Thanks for choosing Got It Pro-Excel.
Excelchat Expert
03/07/2018 - 06:02
Which version of excel are you using?
User
03/07/2018 - 06:03
I need to get data from several workbooks-- (different spreadsheets). I am trying to get all of them in a single workbook in different sheets.
User
03/07/2018 - 06:03
Excel 2016
Excelchat Expert
03/07/2018 - 06:03
Okay.
User
03/07/2018 - 06:04
Excel numbers which i am stuck with:
User
03/07/2018 - 06:04
89011704252314501234
89011704252314505678
89011704252314500910
89011704252314501112
Excelchat Expert
03/07/2018 - 06:04
Which file type are you getting the data please?
Excelchat Expert
03/07/2018 - 06:05
I mean which software are you importing data from?
Excelchat Expert
03/07/2018 - 06:05
Is it still Excel?
User
03/07/2018 - 06:05
I can change the format in the original workbooks -- yes, excel workbooks.
User
03/07/2018 - 06:06
But I would need data from various workbooks (excel spreadsheets) into a single workbook(my own one).
Excelchat Expert
03/07/2018 - 06:06
Will you mind if I have a look at the workbook from which you are importing data from?
User
03/07/2018 - 06:07
how do i share that with you, my friend?
Excelchat Expert
03/07/2018 - 06:08
Before you share, do you mean you want an exact sheet of your original workbook in the new workbook you are creating?
User
03/07/2018 - 06:08
yes.
Excelchat Expert
03/07/2018 - 06:09
Then in that case, you don't need to import from data.
Excelchat Expert
03/07/2018 - 06:09
You can use a different and easier method.
User
03/07/2018 - 06:09
SNN DDDYY KKLL
DB171800567 89011704252314501589 354421074165123
DB171800045 89011704252314501450 354421074106356
DB171800067 89011704252314512564 354421074400456
DB171800072 89011704252314502243 354421074145689
User
03/07/2018 - 06:09
Oh-- please let me know, sir.
User
03/07/2018 - 06:09
I would be grateful to you for that.
Excelchat Expert
03/07/2018 - 06:10
Open the two workbooks; ensure the one from which you are importing data and the one you are creating are open.
Excelchat Expert
03/07/2018 - 06:11
In the workbook from which you are copying a sheet, go to the sheet tabs.
User
03/07/2018 - 06:11
okay...
Excelchat Expert
03/07/2018 - 06:12
Right-click on the sheet you want to copy.
Excelchat Expert
03/07/2018 - 06:12
On the context menu, select Move or Copy option.
Excelchat Expert
03/07/2018 - 06:12
Are you there?
User
03/07/2018 - 06:12
Yes.. I am following you, sir.
Excelchat Expert
03/07/2018 - 06:13
A Move or Copy dialog box pop up. Right?
User
03/07/2018 - 06:13
Yes..
User
03/07/2018 - 06:13
There is a check box for copy.
User
03/07/2018 - 06:13
Do you want me to check that?
User
03/07/2018 - 06:14
there is an option for move to end?
Excelchat Expert
03/07/2018 - 06:14
In the To book: field, select the name of the other workbook to which you want to copy the sheet.
Excelchat Expert
03/07/2018 - 06:14
Check the Create Copy checkbox
User
03/07/2018 - 06:14
Okay-- one moment.
Excelchat Expert
03/07/2018 - 06:15
Okay.
User
03/07/2018 - 06:16
Excellent-- i have got that.
User
03/07/2018 - 06:16
And if i have multiple sheets-- i just need to select all sheets. Right?
Excelchat Expert
03/07/2018 - 06:16
Did you click on the "Move to the End"?
User
03/07/2018 - 06:16
Also, i have a question here-- so, the data keeps added to the main sheets on a daily basis.
User
03/07/2018 - 06:17
Is there a way, that the data gets updated on the new workbook (my personal one) without the main sheet being affected.
User
03/07/2018 - 06:18
No i have not clicked the move to end
Excelchat Expert
03/07/2018 - 06:18
Okay. First have you known how to copy a sheet to another workbook?
User
03/07/2018 - 06:18
Yes, i have got the complete copy of the data.
Excelchat Expert
03/07/2018 - 06:19
Very good.
Excelchat Expert
03/07/2018 - 06:19
So what is the next question/
User
03/07/2018 - 06:19
Yes... So, i need to get the data updated.
User
03/07/2018 - 06:20
For example-- if someone adds data on the main sheet, how would that get updated in the new workbook which i created?
Excelchat Expert
03/07/2018 - 06:20
Okay. I get you now.
Excelchat Expert
03/07/2018 - 06:20
That is a complex issue.
Excelchat Expert
03/07/2018 - 06:21
It will depend where the files are stored.
Excelchat Expert
03/07/2018 - 06:21
In same computer, or different computers or over a network.
User
03/07/2018 - 06:22
It is like we do share a network and My file will also be in the network folders.
User
03/07/2018 - 06:22
Irrespective of which system i use, i can get access to that folder and excel file.
Excelchat Expert
03/07/2018 - 06:22
Okay.
Excelchat Expert
03/07/2018 - 06:23
That one can be best achieved using VBA.
User
03/07/2018 - 06:24
Oh my goodness.. I don't much of VBA program. Is there a pre-written program for this kind of situation?
User
03/07/2018 - 06:24
And in case if it is the same computer, do we have a work around/
Excelchat Expert
03/07/2018 - 06:24
Nop, it will have to be customized.
User
03/07/2018 - 06:24
Oh okay-- i understand that.
Excelchat Expert
03/07/2018 - 06:25
There is no workaround, you must use VBA if you want it to automatically update the files.
Excelchat Expert
03/07/2018 - 06:25
That is automatically and regularly.
User
03/07/2018 - 06:25
I'd have a dedicated system if you think there is a work around for data to get auto populated from different workbooks.
Excelchat Expert
03/07/2018 - 06:25
But another approach will be to create a macro.
User
03/07/2018 - 06:25
Okay-- i understand.
User
03/07/2018 - 06:26
Do you have any link for macro to be set up for a situation like this, sir?
User
03/07/2018 - 06:26
I have worked on macros some time before.
Excelchat Expert
03/07/2018 - 06:27
The weakness of a macro without VBA will be that you will have to run it manually and you personally to update the file.
User
03/07/2018 - 06:27
Okay-- i completely understand that, sir.
User
03/07/2018 - 06:28
yes, i will check for the available options.
Excelchat Expert
03/07/2018 - 06:28
That is greate.
User
03/07/2018 - 06:28
Thanks for your time and help offereed.
User
03/07/2018 - 06:28
have a wonderful day/evening.
Excelchat Expert
03/07/2018 - 06:28
It was my pleasure helping you solve this problem. Any time you have a problem with Excel do not hesitate to come back for help. Enjoy the rest of your day!
User
03/07/2018 - 06:28
Sure.
User
03/07/2018 - 06:28
Same to you.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
