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