Copied, Edited Macro partially works gives run-time error

batador

New Member
Joined
Apr 15, 2013
Messages
17
I am running Excel 2007 on Windows XPProfessional. I have copied and edited a macro from another program and it will work the first time I run it. When I try to run it again, it gives me a run-time error of " '1004' Microsoft Office Excel cannot insert columns because the last column (column IV) contains data. You will not get any data."

I am new to programming, no schooling, only self-taught, and am struggling through this. I think I need an If statement that looks to see if data is in place and if so to insert 16 new rows for new data populated from a tab within the workbook and data I am pulling from a SQL database. When I click on debug on the error message it takes me to this line of code within the macro:

With ActiveSheet.QueryTables.Add(server, _
Destination:=Range("B2"))
.Sql = (strSQL)
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = True
End With

Anyone willing to help?
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Welcome to MrExcel.

If the QueryTable already exists why are you adding another rather than refreshing the existing one? You could try:

Code:
On Error Resume Next
ActiveSheet.QueryTables(1).Delete
On Error GoTo 0

before adding it again.
 

batador

New Member
Joined
Apr 15, 2013
Messages
17
The part that says "ActiveSheet.QueryTables.Add" I am assuming is referring to our SQL db and not the excel file I created. So a quick explanation as to why not refresh: The SQL table contains performance numbers and does not have a person associated with those numbers. It would be hugely time consuming to change the tables, what I was told anyway, and I am not sure how to create a shift schedule that would interface in that way with our SQL db. So I modified our current shift schedule, which we use excel for, and figured out how to make it talk with our SQL db, with the idea in mind to create an excel db or an access db for tracking a person's performance numbers. It was everything I could do to make that work, cause like I said, I am greener than green with this stuff. I hope that answers your question.
 

batador

New Member
Joined
Apr 15, 2013
Messages
17

ADVERTISEMENT

Ok, that explains a little. I have been staring at this thing all night. I learned a little more after you clarified that. Where exactly would I try to place the code you suggested?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Just put those three lines of code before:

Code:
With ActiveSheet.QueryTables.Add(server, _
      Destination:=Range("B2"))
 

batador

New Member
Joined
Apr 15, 2013
Messages
17

ADVERTISEMENT

I tried and still get the same error. I am doing something else wrong apparently. :(
 

batador

New Member
Joined
Apr 15, 2013
Messages
17
Not sure what you mean by query table. In the excel workbook I have ten tabs. The first is a shift schedule where I assign a person's name to a job. The other one is the one I am really concerned about and that is where I want the names to populate and the data from the sql db associated with each job to populate.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Your code is adding a QueryTable to the active sheet. So with that sheet active what does this return when typed in the Immediate window?

?ActiveSheet.QueryTables.Count
 

Watch MrExcel Video

Forum statistics

Threads
1,123,474
Messages
5,601,880
Members
414,479
Latest member
Beau the dog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top