Remove Query Connections to Excel Workbook Copy 1

[shelby55]

Hi

I am using Excel 2003. The worksheet with raw data (WSD) is automatically updating based on a query from Access.

With Skip's help I've been able to make a copy of my current workbook with the raw data being saved and several protected cells. The code is below:

'this keeps the copy from being displayed
    Application.ScreenUpdating = False 
 
   Sheets(Array("WSA", "WSB", "WSC", "WSD", "WSE", "WSF")).Copy
    Set NewWB = ActiveWorkbook
    Application.DisplayAlerts = False
    Application.CutCopyMode = False
    
    with NewWB.Sheets("WSD")
'unlock ALL cells
       With .Cells
          .Locked = False
          .FormulaHidden = False
       end with
'lock these cells
       with .Range("L4:M40")
           .Locked = True
           .FormulaHidden = False
       end with
       .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    end with
    NewWB.SaveCopyAs Filename:="E:\My Documents\MyWB_" & Format(Now(), "YYYYMMDD") & ".xls"
    ActiveWindow.Close
    Application.ScreenUpdating = True

Now I would like to ensure that the connection to Access is removed so that the copy doesn't try to query Access. I've asked this before but none of the suggestions worked.

All assistance greatly appreciated - thanks.

 

[SkipVought]

Hi,

Delete the QueryTable...

  dim qt as querytable

  with NewWB.Sheets("WSD")
    for each qt in .querytables
      qt.delete
    next
.....
  end with

or if its 2007...

  dim qt as listobject

  with NewWB.Sheets("WSD")
    for each qt in .listobjects
      qt.querytable.delete
    next
.....
  end with

Skip,

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

 

[shelby55]

Hi Skip

Thank you. There is only one query in one worksheet but do I do it the way you suggested anyway? Also it is Excel 2003.

 

[shelby55]

P.S.

Sorry but where is this placed within the original code?

 

[SkipVought]

anywhere within the WITH NewWB.Sheets("WSD")...End With.

    with NewWB.Sheets("WSD")
'unlock ALL cells
       With .Cells
          .Locked = False
          .FormulaHidden = False
       end with
'lock these cells
       with .Range("L4:M40")
           .Locked = True
           .FormulaHidden = False
       end with
       .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    end with

Skip,

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

 

[shelby55]

Hi Skip

YOU ARE AMAZING! It worked and this is the last thing I needed to do for this project - thanks so very much!

Shelby

 

[shelby55]

Hi Skip

When opening the copy I get a dialog box indicating "this worksheet contains links to other data sources" and then asking to update, don't update, help".

How can I get rid of this dialog box...plus why am I getting one if I'm removing the connection in the code?

Thanks.

 

[shelby55]

Hi

Believe it or not I figured this out for myself. I added the code of NewWB.UpdateLinks = xlUpdateLinksNever above the copy workbook line and now it works as it should!