Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overwriting a table with another query
Hi there,
So I am receiving this error: '1004' A table cannot overlap a range that contains another table... I am creating a vb macro to obtain data and write this data to different specific sheets ( which may already contain data ) I am importing data from microsoft query. The table is then created from this data, and is dynamic in size, tho when data exists, it doesnt seem to work. Im assuming the best way to get around this error, is to delete the table that exists, if there is one. Is there some specific VB code that will allow me to do this? Any help is greatly appreciated! Thanks! - jacques |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overwriting a table with another query
For Each itm In ActiveSheet.Names
itm.Delete Next itm "Jacques Q" wrote: Hi there, So I am receiving this error: '1004' A table cannot overlap a range that contains another table... I am creating a vb macro to obtain data and write this data to different specific sheets ( which may already contain data ) I am importing data from microsoft query. The table is then created from this data, and is dynamic in size, tho when data exists, it doesnt seem to work. Im assuming the best way to get around this error, is to delete the table that exists, if there is one. Is there some specific VB code that will allow me to do this? Any help is greatly appreciated! Thanks! - jacques |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overwriting a table with another query
Ok so here is my actual code.
Sheets("RickSales").Select For Each itm In ActiveSheet.Names itm.delete Next itm It runs without error, tho it still doesnt seem to delete anything, i.e find any items in the ActiveSheet, any other ideas? Thanks again! - Jacques "joel" wrote: For Each itm In ActiveSheet.Names itm.Delete Next itm "Jacques Q" wrote: Hi there, So I am receiving this error: '1004' A table cannot overlap a range that contains another table... I am creating a vb macro to obtain data and write this data to different specific sheets ( which may already contain data ) I am importing data from microsoft query. The table is then created from this data, and is dynamic in size, tho when data exists, it doesnt seem to work. Im assuming the best way to get around this error, is to delete the table that exists, if there is one. Is there some specific VB code that will allow me to do this? Any help is greatly appreciated! Thanks! - jacques |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overwriting a table with another query
hi
it's not finding items ON the sheets. it's finding named ranges and deleting them. excel considers microsoft query as a named range. do you still have named ranges in the name box? or in the define name dialog? can you run your original code without error now? i ran the code on a test file. worked. Regards FSt1 "Jacques Q" wrote: Ok so here is my actual code. Sheets("RickSales").Select For Each itm In ActiveSheet.Names itm.delete Next itm It runs without error, tho it still doesnt seem to delete anything, i.e find any items in the ActiveSheet, any other ideas? Thanks again! - Jacques "joel" wrote: For Each itm In ActiveSheet.Names itm.Delete Next itm "Jacques Q" wrote: Hi there, So I am receiving this error: '1004' A table cannot overlap a range that contains another table... I am creating a vb macro to obtain data and write this data to different specific sheets ( which may already contain data ) I am importing data from microsoft query. The table is then created from this data, and is dynamic in size, tho when data exists, it doesnt seem to work. Im assuming the best way to get around this error, is to delete the table that exists, if there is one. Is there some specific VB code that will allow me to do this? Any help is greatly appreciated! Thanks! - jacques |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overwriting a table with another query
First, thanks for the quick replies!
On another note, is it relevant if im using excel 2007? The named ranges are still there in the name box, and all the tables are still on the sheet. Still runs without error. Again I appreciate any other ideas, thanks! - jacques "FSt1" wrote: hi it's not finding items ON the sheets. it's finding named ranges and deleting them. excel considers microsoft query as a named range. do you still have named ranges in the name box? or in the define name dialog? can you run your original code without error now? i ran the code on a test file. worked. Regards FSt1 "Jacques Q" wrote: Ok so here is my actual code. Sheets("RickSales").Select For Each itm In ActiveSheet.Names itm.delete Next itm It runs without error, tho it still doesnt seem to delete anything, i.e find any items in the ActiveSheet, any other ideas? Thanks again! - Jacques "joel" wrote: For Each itm In ActiveSheet.Names itm.Delete Next itm "Jacques Q" wrote: Hi there, So I am receiving this error: '1004' A table cannot overlap a range that contains another table... I am creating a vb macro to obtain data and write this data to different specific sheets ( which may already contain data ) I am importing data from microsoft query. The table is then created from this data, and is dynamic in size, tho when data exists, it doesnt seem to work. Im assuming the best way to get around this error, is to delete the table that exists, if there is one. Is there some specific VB code that will allow me to do this? Any help is greatly appreciated! Thanks! - jacques |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overwriting a table with another query
Make sure the correct page is active. You can replace activesheet with
Sheets("RickSales"). Make sure you don't have any other workbooks opened. Check if you have any megeed cells. highlight the entire worksheet and go to format - Cells - alignment and make sure merged cells isn't checked. For Each itm In Sheets("RickSales").Names itm.delete Next itm "Jacques Q" wrote: First, thanks for the quick replies! On another note, is it relevant if im using excel 2007? The named ranges are still there in the name box, and all the tables are still on the sheet. Still runs without error. Again I appreciate any other ideas, thanks! - jacques "FSt1" wrote: hi it's not finding items ON the sheets. it's finding named ranges and deleting them. excel considers microsoft query as a named range. do you still have named ranges in the name box? or in the define name dialog? can you run your original code without error now? i ran the code on a test file. worked. Regards FSt1 "Jacques Q" wrote: Ok so here is my actual code. Sheets("RickSales").Select For Each itm In ActiveSheet.Names itm.delete Next itm It runs without error, tho it still doesnt seem to delete anything, i.e find any items in the ActiveSheet, any other ideas? Thanks again! - Jacques "joel" wrote: For Each itm In ActiveSheet.Names itm.Delete Next itm "Jacques Q" wrote: Hi there, So I am receiving this error: '1004' A table cannot overlap a range that contains another table... I am creating a vb macro to obtain data and write this data to different specific sheets ( which may already contain data ) I am importing data from microsoft query. The table is then created from this data, and is dynamic in size, tho when data exists, it doesnt seem to work. Im assuming the best way to get around this error, is to delete the table that exists, if there is one. Is there some specific VB code that will allow me to do this? Any help is greatly appreciated! Thanks! - jacques |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overwriting a table with another query
Still no luck, no merged cells. Macro still runs, but still does nothing.
"joel" wrote: Make sure the correct page is active. You can replace activesheet with Sheets("RickSales"). Make sure you don't have any other workbooks opened. Check if you have any megeed cells. highlight the entire worksheet and go to format - Cells - alignment and make sure merged cells isn't checked. For Each itm In Sheets("RickSales").Names itm.delete Next itm "Jacques Q" wrote: First, thanks for the quick replies! On another note, is it relevant if im using excel 2007? The named ranges are still there in the name box, and all the tables are still on the sheet. Still runs without error. Again I appreciate any other ideas, thanks! - jacques "FSt1" wrote: hi it's not finding items ON the sheets. it's finding named ranges and deleting them. excel considers microsoft query as a named range. do you still have named ranges in the name box? or in the define name dialog? can you run your original code without error now? i ran the code on a test file. worked. Regards FSt1 "Jacques Q" wrote: Ok so here is my actual code. Sheets("RickSales").Select For Each itm In ActiveSheet.Names itm.delete Next itm It runs without error, tho it still doesnt seem to delete anything, i.e find any items in the ActiveSheet, any other ideas? Thanks again! - Jacques "joel" wrote: For Each itm In ActiveSheet.Names itm.Delete Next itm "Jacques Q" wrote: Hi there, So I am receiving this error: '1004' A table cannot overlap a range that contains another table... I am creating a vb macro to obtain data and write this data to different specific sheets ( which may already contain data ) I am importing data from microsoft query. The table is then created from this data, and is dynamic in size, tho when data exists, it doesnt seem to work. Im assuming the best way to get around this error, is to delete the table that exists, if there is one. Is there some specific VB code that will allow me to do this? Any help is greatly appreciated! Thanks! - jacques |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Web Query and table? | Excel Programming | |||
Use a button to create a table in excel without overwriting cells | Excel Programming | |||
VB way to remove the query from a Query Table? | Excel Programming | |||
Overwriting/Overlapping web query problem | Excel Programming | |||
Linking a table in Access to a table in Excel using MS Query | Excel Programming |