+ Reply to Thread
Results 1 to 7 of 7

Error when importing data to a second workbook through

  1. #1
    Registered User
    Join Date
    08-31-2016
    Location
    Manchester
    MS-Off Ver
    2013
    Posts
    3

    Error when importing data to a second workbook through

    Hi,

    I have a table in one excel sheet (which is located on a shared drive). This table has around 30,000 rows and is updated with new information every day (around 50 rows worth is added every day), using VBScript.

    I have successfully imported this information into a local workbook, which is fine, however I am encountering errors with this when I try to refresh.

    When I do not have the original workbook open, I am greeted with an error when trying to refresh - shown below.

    exceltableerror.png

    Transcribed - "We couldn't get the data from 'Table2' in the workbook [path blanked for privacy]. Open this workbook in Excel and try again."

    When I do have the original workbook open, the data refreshes fine. Whilst I am thinking the other solution is to create a VBScript that opens both workbooks, updates the second one, then closes both workbooks, this is not really the ideal solution.

    Some housekeeping - I have 100% ensured the correct workbook is being referenced, also, this would give the "workbook not found error".

    Can anyone help explain why i'm seeing this error? I've done a fair bit of googling, and many people are referring to power pivots/tables, however i am on 2013 and not using these methods.

    Thanks in advance.

  2. #2
    Forum Contributor
    Join Date
    08-23-2016
    Location
    India
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    194

    Re: Error when importing data to a second workbook through

    Click on pivot. Go to ribbon tool and click change data source and remove path/file name from reference and just keep the table name.
    Ash

  3. #3
    Registered User
    Join Date
    08-31-2016
    Location
    Manchester
    MS-Off Ver
    2013
    Posts
    3
    Quote Originally Posted by Ash_Maverick View Post
    Click on pivot. Go to ribbon tool and click change data source and remove path/file name from reference and just keep the table name.

    Hi ash, thanks for your reply. However I am not using a pivot table in either document. I simply have a table in the first document, which I can transfer to the second document. However it will only update when the first document is open.

    Thanks

  4. #4
    Forum Contributor
    Join Date
    08-23-2016
    Location
    India
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    194

    Re: Error when importing data to a second workbook through

    what is the path for the referenced file in the cells..............i mean how does it look(just the file name or absolute path) ?

  5. #5
    Forum Contributor
    Join Date
    08-23-2016
    Location
    India
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    194

    Re: Error when importing data to a second workbook through

    are there formulas in the referenced file ?

  6. #6
    Registered User
    Join Date
    08-31-2016
    Location
    Manchester
    MS-Off Ver
    2013
    Posts
    3

    Re: Error when importing data to a second workbook through

    Quote Originally Posted by Ash_Maverick View Post
    what is the path for the referenced file in the cells..............i mean how does it look(just the file name or absolute path) ?
    The path references the workbook on a shared drive - now sure how else i can explain this - it's an absolute file path like this -> C:/This/File/Path/File.xlsx

    Quote Originally Posted by Ash_Maverick View Post
    are there formulas in the referenced file ?
    There are formulas, in the last column.



    UPDATE - I have fixed this, explaining below to help others who may encounter this extremely frustrating issue.

    Not sure what is causing this issue, but I have managed to solve it by importing the table in a slightly different way.

    1. Click data menu tab.
    2. Click existing connections.
    3. Click browse for more.
    4. Find the file you wish to import your table from, in the file explorer and click open.
    5. This results in a prompt asking which tab you wish to import the table from.
    6. Done - I am now able to refresh the data whilst the workbook is closed, with no issues.

    Thanks,
    Luke

  7. #7
    Registered User
    Join Date
    04-25-2016
    Location
    India
    MS-Off Ver
    2010
    Posts
    34

    Thumbs up Re: Error when importing data to a second workbook through

    Hello folks,

    This is quite an old thread but people (like me) keep facing it once in a while & so keep visiting this thread, it seems...

    Needless to say, I also faced & kept on searching the web (incl. this site more frequently) having no luck towards solution...

    ...but, just as I was trying huberu's steps above (mind you, these are still the best written guidelines even today) for fifth time or so, something struck me..

    I noticed that my 'master' table (which is placed in a separate sheet) had a number of named ranges few of which were duplicates (i.e. referring to the same set of cells). I deleted the duplicates... I also noticed that my master table had the 'Total' row, which was dysfunctional. So, I removed it (select any cell inside the table -> 'Table tools DESIGN' tab -> Table style options -> Uncheck 'Total Row'

    These two things solved my 'We couldn't get....' problem.

    While I don't say it's THE solution, but you can try it out. I'm just writing them in shorter manner -
    1. Check your master table for any duplicate named ranges & remove the duplicates.
    2. Check the 'Total Row' is really required to you. If not, remove it.

    May the ladyluck smile on you!
    Last edited by 3xe1; 10-01-2017 at 02:10 PM.
    As I learn more,I learn there's even more to learn

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Importing Data from another workbook
    By Southernw2002 in forum Excel General
    Replies: 7
    Last Post: 08-07-2016, 12:59 PM
  2. Importing data from old workbook into updated version of same workbook
    By jontherev in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-28-2015, 10:11 AM
  3. Importing data from one workbook to another
    By nooredein in forum Excel General
    Replies: 1
    Last Post: 04-24-2014, 11:32 AM
  4. Importing and Exporting data from workbook to workbook in excel
    By dans123 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-09-2012, 03:40 AM
  5. Error #13 during importing data. query error?? or wot?
    By sspandit in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-02-2010, 06:02 AM
  6. importing data into a workbook
    By PizzaBoy in forum Excel Formulas & Functions
    Replies: 44
    Last Post: 09-06-2005, 09:05 PM
  7. [SOLVED] Importing Data from unopened Workbook into an open Workbook
    By GrayesGhost in forum Excel General
    Replies: 0
    Last Post: 03-05-2005, 08:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1