I want to add data from a another (non JET) spreadsheet to my JET report, automatically.
I have a simple JET report that compiles data on our Suppliers. I want to add some additional Suppliers from a list that is kept in another spreadsheet (theses suppliers are not in the Navision Dbase).
I want the report to have 'values' in it, not formula's or references or links to external files or other tabs.
I do not want to simply 'copy paste special' the external list into the JET sheet prior to running the report, as I am not always here to do this and there are often changes to this external data.
I do not want the JET report to be full of external references, or links to other tabs as I want real 'values' so that they can be copy pasted easier by people who end up using the report and who are not very good with Excel.
I tried the =NP("Eval","external sheet") function but it wont work like other 'relative' xl references when I drag and drop the formula. It appears to work on an Absolute reference basis only.
I cant do Macros or programming
Any ideas please
Thank you
Date
Votes
2 comments
-
Jet Reports Historic Posts Hi!
This should be possible by creating an ODBC data source to the other Excel workbook. You'll need a Jet Reports universal connector and you may need to mess with the format of the data in your other workbook a bit, but it should work.
First, in Windows you go to Control Panel -> Administrative Tools -> Data Sources (ODBC). Then you need to add a User or System DSN and select the Microsoft Excel Driver. Give it a name and browse to the workbook and you're done. Then in Excel to go your Jet data source settings a create a new universal data source. Configure the data source and select the OLE DB Provider for ODBC Drivers. Then select the name of the Excel ODBC data source you just created.
Basically with this sort of data source, the names of the Excel worksheets will appear as tables. If you have data on a sheet, you should see the first row of data on the sheet appear as the names of the fields in the data source. Then you can use Jet functions to pull the data from that workbook to your report. Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts Hughes
I appeciate you taking the time to help.
Unfortunately I could not make the connection, but will wait for the next time our visiting IT chap calls. He may know why.
Thanks again
John
Please sign in to leave a comment.