Excel 2003: External data from another xls missing

[Auger282]

I have a really pain issue right now. I'm thinking its a version issue or some hidden option somewhere. Let me start this with it works on two people's computers and not on mine or anyone elses....

I'm trying to pull a whole spreadsheet into an different workbook using the external data function.

The problem is that when I run the refresh I get all the cols that are values and vlookups but I have cols that are currency that are not coming in at all... they are sumif statements and then calculations off the sumif cols.

3 cols that come in and 7 that do not... instead of the data I'm expecting I get a col of "1"s

Let me reiderate.. this is working as is on two machines but not on the rest of ours..

The source can be updated and saved.. no issues or error msgs they have all the data as they should. The destination at this point is a blank.. brand spankin new xls file and the darn thing doesnt pull it all in.

I'm not sure whats going on here.. all windows updates are installed and ms query is installed. We are using the microsoft excel odbc that is installed with office 2003. The source spreadsheet is 2003 as well.

If there is any other information I'm missing please feel free to ask.. any help is greatly appreciated.

 

[SkipVought]

Hi,

Are you referring to ONE workbook that has a sheet with a QueryTable querying data from an external workbook?

This ONE workbook query, works on two people's PCs but not on anyone elses; correct?

Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[Auger282]

I have a source workbook that does all sorts of magic within it self that works on everyones machine.

The destination workbook is trying to use the External data function to pull a spreadsheet from the source workbook into the destination workbook. Once that source sheet is pulled in all sorts of magic happen to that raw data onto other sheets in the destination workbook.

They suck the sheet in before they mangle it around rather then doing a direct lookup to the other workbook.

The sheet that it gets sucked into is blank and should reflect all the data that is in the source sheet (no query its just pulling everything)

On the two machines they get all the data... on everyone else's ... we dont get the currency cols.. that are all calculated via sumifs

there are no cross calculations the only transfer between workbooks is the pull of a whole sheet from source to destination

 

[SkipVought]

So there's ONE source and MANY destination workbooks? YES?

What is the "external data function" that is employed to get the data from the source workbook into each destination workbook. Is this a PUSH or PULL task?


Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[Auger282]

one to one

in Excel 2003
Data - Import External Data - Import data
or
Data - Import External Data - New Database Query

its a pull
grabs the source sheet and pastes it in the destination sheet where I specify

 

[SkipVought]

So there's ONE destination workbook, that if TWO users open and refresh, see all the data, but everyone else does not? YES?

So is it an IMPORT or a Database QUERY? Its got to be one or the other.

Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[Auger282]

So there's ONE destination workbook, that if TWO users open and refresh, see all the data, but everyone else does not? YES?

-Correct... independently.. not at the same time

So is it an IMPORT or a Database QUERY? Its got to be one or the other.

Database query is what we are using.. however doing either of the above operations provide the same result with missing data on the machines that are having the issue

 

[SkipVought]

What is the SQL code for the query? Please post.

Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[Auger282]

This is what is listed under "Edit OLE DB Query"

Connection:
Provider=Microsoft.Jet.OLEDB.4.0
User ID=Admin
Data Source=R:\SOURCE.xls
Mode=Share Deny Write
Extended Properties="HDR=YES;"
Jet OLEDB:System database=""
Jet OLEDB:Registry Path=""
Jet OLEDB:Engine Type=35
Jet OLEDBatabase Locking Mode=0
Jet OLEDB:Global Partial Bulk Ops=2
Jet OLEDB:Global Bulk Transactions=1
Jet OLEDB:New Database Password=""
Jet OLEDB:Create System Database=False
Jet OLEDB:Encrypt Database=False
Jet OLEDBon't Copy Locale on Compact=False
Jet OLEDB:Compact Without Replica Repair=False
Jet OLEDB:SFP=False

Command Type:
Table

Command Text:
'Source Table $'

 

[SkipVought]

I suspect...

"Data Source=R:\SOURCE.xls"

is NOT a the best way to write this statement, since not everyone could have Drive R mapped identically.

It would be better to explicitly define the SERVER, like...
[tt]
Data Source=\\someserver\SOURCE.xls
[/tt]

Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[Auger282]

I agree going forward

currently all users working and non working have the same drive mappings

I may end up setting this up as a linked workbook.. but its annoying because it works on 2 systems and not on mine and the person who needs this... there has to be a reason and a solution

 

[SkipVought]

Do you have the R drive mapped to the serve on which this workbook resides?

Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[Auger282]

yes

the R drive is where both the source and the destination workbooks live

 

[SkipVought]

Do ANY changes in the source workbook, show up in the destination when YOU open and refresh?

Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[Auger282]

what I've done to test this is clear out the data that is already on the destination sheet and click the "refresh data" button on the "external data" toolbar.

The data gets pulled in as described above.. 1/2 the sheet comes in. It appears that all the vlookup data comes in fine but the sumif currency cols are missing.

the first col where I would expect currency data there is a col of "1"s

 

[SkipVought]

Is it returned as TEXT or NUMERIC ones? Check the cell format.

Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[SkipVought]

"Edit OLE DB Query"

Could you tell me what DRIVER is configured in Start > Settings > Control Panel > Administrative Tools

I have not found how to add an OLD DB query. I do know how to add an ODBC query or a new ODBC Driver. I need to be educated.

Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[Auger282]

Everything is pulled into the destination as a General type of cell....

 

[SkipVought]

OLE DB, not OLD!

Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[Auger282]

Turn on your External data tool bar

then setup an external data pull

Data -> external data -> New database query

Databases-> excel files -> OK

Find the source file then in the "query wizard - choose colums" grab the sheet you want. Next I do not do any query I want the whole thing.. so I just go next next next..

What do you want to do next...
Return data to microsoft office excel - Finish

then where do you want to put it.. grab a cell and say OK

All the data you specified will be pulled in and then your External Data toolbar will be lit up..

If you hit the Edit query button you will either get the screens you had before (if you have ms query installed) or you will get the "Edit OLE DB Query" window that has the same info in a more down and dirty view