Copied, Edited Macro partially works gives run-time error

[batador]

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?

 

[Andrew Poulsom]

Welcome to MrExcel.

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

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

before adding it again.

 

[batador]

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.

 

[Andrew Poulsom]

No, ActiveSheet.QueryTables.Add is referring to the active sheet in Excel.

 

[batador]

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]

Just put those three lines of code before:

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

 

[batador]

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

 

[Andrew Poulsom]

How may QueryTables do you have on the worksheet?

 

[batador]

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]

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