Overite Cells VBA Query Code

**zhb12810** · 04-15-2012, 03:53 AM

Hi,

I currently have the following VBA code that imports data from a Dbf file into an Excel spreadsheet but even though it clears the sheet before i run it if i run if twice it "inserts" cells shift the remaining cells to the right

Any ideas of how to set the code to overate existing cells would be great

Please Login or Register  to view this content.

Thanks in advance

**Firefly2012** · 04-15-2012, 03:57 AM

Hi

You can programmatically set the RefreshStype property - eg before your oQT.Refresh line include the line:

Please Login or Register  to view this content.

**zhb12810** · 04-15-2012, 01:43 PM

Originally Posted by Firefly2012

Hi

You can programmatically set the RefreshStype property - eg before your oQT.Refresh line include the line:

Please Login or Register  to view this content.

Thanks for this but when running the code i get an Run-Time error 1004, Cannot complete operation: A table cannot overlap with a pivot table report, query results, a table, merged cells, or an XML mapping

Any ideas??

Thanks

**Firefly2012** · 04-16-2012, 01:00 AM

If you're doing this in xl2010 then usually the querytable is contained within a ListObject, so try amending your querytable delete code to:

Please Login or Register  to view this content.

**Firefly2012** · 04-16-2012, 04:14 AM

Ignore my previous post - I don't think it is relevant to your situation. i think all you need to do is ensure you refresh the data synchronously (rather than asynchronously as you have it at the moment). I think what is happening is that when you click it a second time, the first refresh hasn't completed meaning you end up with two query tables on the sheet. You can avoid this happening by amending your Refresh line to:

Please Login or Register  to view this content.

**zhb12810** · 04-16-2012, 04:34 PM

Thanks for all of these but I'm still getting cells inserted rather than overwritten when running the code more than once!!

I've tried differnt combinations of the code but still no joy.

Thanks anyway

**Firefly2012** · 04-17-2012, 04:13 AM

Did you try combining?

Please Login or Register  to view this content.

Also, i you have the sheet open that contains the returned data, can you open the VBE, open up the Immediate Window (Ctrl+g) and type into it:

Please Login or Register  to view this content.

Don't omit the leading ?. Please let me know in your reply what it returns.

**zhb12810** · 04-17-2012, 05:54 PM

Hi Firefly,

I've tried the above and still no joy. When i insert the code into the Immediate window it displays 1

Any ideas??

Thanks

Overite Cells VBA Query Code

LinkBack

Thread Tools

Rate This Thread

Display

Overite Cells VBA Query Code

Re: Overite Cells VBA Query Code

Re: Overite Cells VBA Query Code

Re: Overite Cells VBA Query Code

Re: Overite Cells VBA Query Code

Re: Overite Cells VBA Query Code

Re: Overite Cells VBA Query Code

Re: Overite Cells VBA Query Code

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions