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?
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Just put those three lines of code before:

Code:
With ActiveSheet.QueryTables.Add(server, _
      Destination:=Range("B2"))
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,211,802
Messages
6,104,058
Members
447,892
Latest member
madara uchiha

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
Back
Top