Database Query Multiple Dates

Flavoursavor

New Member
Joined
Feb 18, 2009
Messages
7
Hello All,

I've created a macro that queries an AS400 database and returns values on the first sheet. My macro has the option for 1-Today, 2-Other day, 3-Range.

When I select option 3-Range, I wish to have each of the days I defined in the range show up on seperate sheets. The macro successfully creates the first sheet, but as I'm working through the code, I get a runtime error '1004' as my query starts to run for the second day.

I'm assuming that the macro is only looking at the first sheet and therefore does not want to write to the second.

Private Sub runGather(sconn As String, sQuery As String)
'gather data from the as400
Range("A1").Select
My code breaks at this line.

Rows("2:65500").Select
Selection.Delete Shift:=xlUp
With ActiveSheet.QueryTables.Add(Connection:=sconn, Destination _
:=Range("A2"))
.CommandText = sQuery
.Name = "Query from AS400_1"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
End With
End Sub


Thanks for any help!
JF
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

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