Excel Import Adding Extra Column

[shelby55]

Hello

I'm using Excel 2003 accessing data from an Access 2003 database.

I have a workbook with a "raw data" table which feeds other graphs and tables in the workbook. The data is auto-imported upon open from an Access query.

The workbook was developed by someone else who had columns A to AV as data columns and then columns AW to DQ as formulae columns. The entire raw data worksheet is in a named range and there are named ranges for all of the data.

I had to add columns between AV to AW so now the formulae fields start at BC. However, now when importing the data, it adds an extra column at BB and moves everything else over so the data isn't correct now.

I know there isn't an extra column in the Access query because if I manually copy a few days of data from the Access query(the query is grouped by visit date) then I can paste in Excel and see that no extra columns exist and the columns line up as they should.

What could be causing this? Thanks.

 

[kjv1611]

How is the data being imported? Is it MS Query or a VBA script, or what? Is it possible to redo the import method altogether?

From the sounds of it, I'm assuming that redoing the import is not something you readily want to do.

If it comes down to it, I suppose you could create a VBA script to readjust where the data is... or adjust the formulae if need be, but that may very well be a cumbersome task.

Also, are you sure you need all of that data on one worksheet? It SOUNDS like a lot, but I may be misreading something.

 

[SkipVought]

However, now when importing the data...

How is the data being imported?

Could you not run the same query that MS Access runs IN EXCEL, using MS Query, and entirely avoid those process steps?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip/KJV

It's actually using an ADO process but I can't find the error (but I'm really not sure what I'm looking for).

Everyone is nervous about changing this part of the process because of other issues which is why I don't want to move to the MS-Query route though tests show that doing it that way doesn't result in an extra column added.

Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\myfile\mydb.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;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 OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

Thanks.

 

[SkipVought]

This is your connection string.

Where is your SQL or Command Text?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Sorry I believe what you want is:

' Set the raw data query to pull one year of data from now
' then refresh
Private Sub Workbook_Open()
    
   Dim oQryTable As QueryTable
    
   Set oQryTable = Worksheets("Raw Data").QueryTables(1)
    
   Dim startDate As Date
   Dim endDate As Date
    
   startDate = DateAdd("yyyy", -1, Date)
   endDate = Date
    
   Dim sql As String
    
   sql = "SELECT * " & _
         "FROM DartData " & _
         "WHERE [Date] BETWEEN #" & startDate & "# AND #" & endDate & "#"
    
   oQryTable.CommandText = sql
    
   oQryTable.Refresh False
   AssignMaxDate
    
End Sub

Thanks.

 

[SkipVought]

Is there a field heading for the "added" column of concern?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

shelby55,

What you need to do, if you've not already done it, before you proceed any further, is make a copy of everything (if there are multiple files involved, then all the files to be sure). Copy them to a separate folder or something, call it something like "MyProject_dev" - or whatever that might let you and/or others know it isn't the live version.

Then whatever changes you try will not affect the production work, but rather only your copy. So, if it copies, you can simply delete and start over again. If it works, you can then implement the same thing on the live products.

 

[shelby55]

Thanks KJV - that is how I always work, have a test environment and one that stays as is.

Skip - the worksheet that the data is being pulled into has headers so I don't want headers. However, the extra column gets added, doesn't have a header but data plunks into it. This, of course, means that subsequent columns of data have the wrong data plunking in.

Thanks.

 

[SkipVought]

Excel does not make data out of thin air!

Look in your source data. You are getting a resultset for ALL FIELDS in [DartData]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}