Have a spreadsheet generated by idiots in Payroll.
ALL COLUMNS ARE FIXED, NOTHING
CAN BE ALTERED IN TERMS OF
WHERE DATA (FIELDS) FROM QUERY CAN BE POSITIONED.

Someone in HR enters data by hand into the spreadsheet
starting in Cell A10 to Column BW Row ??? - depends
upon how many new hires/fires previous reporting period.
Could be two rows of data, could be 22 rows of data

Have created three Access queries that return data to
the spreadsheet through MS Query (Excel 2000).
Query 1 starts in Cell A10, Query 2 in Cell A20,
and Query 3 in Cell A30.

Data is refreshed automatically every time spreadsheet
is opened, no need for HR morons to
do anything but open the spreadsheet, review the data,
send it along to Payroll, go back to playing Solitaire.
Ideal setup for any HR person.

However, the deal with the number of rows being dynamic in nature
means sometimes the new hires query returns five rows, (qry 1)
terminated employees query returns three rows (qry 2)
and changes in Title query could be ten rows (qry3).
Sometimes it's the inverse.

I need query 1 to ALWAYS return data starting in cell A10. OK fine,
got that.
But if there are only two new hires, and query 2 is set to return the
first
row of data starting at cell A20, that means eight blank rows, and
that's bad.
Each query is a "not matching" query with different sources,
so I can't combine the three queries into one.

When the spreadsheet is sent back to payroll, there can't be any blank
rows.
OF COURSE, HR person could sort the spreadsheet,
thereby deleting blank rows,
but that would take common sense,
which is in short supply up there.

Is there any way to create some kind of array/vlookup that says?
"Query 1 has found seven new hires this reporting period,
returned seven rows of data,
Query 2 begins eight rows (cell A17) from where query 1 started
in cell A10,
and query 3 starts the next row directly below the last row returned
in query 2"
Is that possible? Without any involvement from end-user?
I would prefer not to use macros. Anyting in VBA is fine.

Queries are long strings with multiple comma seperated variables, and dotted
parameters like the example below. Look at the query and look at the strings
that are double quoted. You can break the string into piece so you can add
variables into the queries.

For example

MyString = "abcdefghijklmnopqrstuvwxyz"

Can be changed to

MyString = "abcdefghijklmn" & _
"opqrstuvwxyz"

I did this many times in the example below.

The destination string is not in double quotes

Destination:=Range("A1")

You can change this to something like this
LastRow = Range("A" & rows.Count).end(xlup).Row
StartQueryRow = LastRow + 8
With ActiveSheet.QueryTables.Add( _
.......
.......
.......
.......
Destination:=Range("A" & StartQueryRow)
........
........
.......
.......

Example of Query

With ActiveSheet.QueryTables.Add( _
Connection:=Array(Array("ODBC;" & _
"DSN=MS Access Database;" & _
"DBQ=C:\Documents and Settings\Joel\My Documents\db1.mdb;" & _
"DefaultDir=C:\Documents and Settings\Joel\My Doc"), _
Array("uments;DriverId=25;" & _
"FIL=MS Access;" & _
"MaxBufferSize=2048;" & _
"PageTimeout=5;")), _
Destination:=Range("A1"))
.CommandText = Array( _
"SELECT `Mailing List`.`Mailing ListID`," & _
"`Mailing List`.FirstName," & _
"`Mailing List`.MiddleName," & _
"`Mailing List`.LastName" & _
Chr(13) & "" & Chr(10) & _
"FROM `C:\Documents and Settings\Joel\" & _
"My Documents\db1`.`Mailing List` `Mailing Lis", _
"t`")
.Name = "Query from MS Access Database"
.FieldNames = True
.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
End With
End Sub


"JumboShrimps" wrote:

Have a spreadsheet generated by idiots in Payroll.
ALL COLUMNS ARE FIXED, NOTHING
CAN BE ALTERED IN TERMS OF
WHERE DATA (FIELDS) FROM QUERY CAN BE POSITIONED.

Someone in HR enters data by hand into the spreadsheet
starting in Cell A10 to Column BW Row ??? - depends
upon how many new hires/fires previous reporting period.
Could be two rows of data, could be 22 rows of data

Have created three Access queries that return data to
the spreadsheet through MS Query (Excel 2000).
Query 1 starts in Cell A10, Query 2 in Cell A20,
and Query 3 in Cell A30.

Data is refreshed automatically every time spreadsheet
is opened, no need for HR morons to
do anything but open the spreadsheet, review the data,
send it along to Payroll, go back to playing Solitaire.
Ideal setup for any HR person.

However, the deal with the number of rows being dynamic in nature
means sometimes the new hires query returns five rows, (qry 1)
terminated employees query returns three rows (qry 2)
and changes in Title query could be ten rows (qry3).
Sometimes it's the inverse.

I need query 1 to ALWAYS return data starting in cell A10. OK fine,
got that.
But if there are only two new hires, and query 2 is set to return the
first
row of data starting at cell A20, that means eight blank rows, and
that's bad.
Each query is a "not matching" query with different sources,
so I can't combine the three queries into one.

When the spreadsheet is sent back to payroll, there can't be any blank
rows.
OF COURSE, HR person could sort the spreadsheet,
thereby deleting blank rows,
but that would take common sense,
which is in short supply up there.

Is there any way to create some kind of array/vlookup that says?
"Query 1 has found seven new hires this reporting period,
returned seven rows of data,
Query 2 begins eight rows (cell A17) from where query 1 started
in cell A10,
and query 3 starts the next row directly below the last row returned
in query 2"
Is that possible? Without any involvement from end-user?
I would prefer not to use macros. Anyting in VBA is fine.

Will try it when I get back to work Monday.
Thanx.

On Jul 26, 6:30*am, Joel wrote:
Queries are long strings with multiple comma seperated variables, and dotted
parameters like the example below. *Look at the query and look at the strings
that are double quoted. *You can break the string into piece so you can add
variables into the queries.

For example

MyString = "abcdefghijklmnopqrstuvwxyz"

Can be changed to

MyString = "abcdefghijklmn" & _
* *"opqrstuvwxyz"

I did this many times in the example below.

The destination string is not in double quotes

* * * Destination:=Range("A1")

You can change this to something like this
LastRow = Range("A" & rows.Count).end(xlup).Row
StartQueryRow = LastRow + 8
With ActiveSheet.QueryTables.Add( _
......
......
......
......
* * * Destination:=Range("A" & StartQueryRow)
.......
.......
......
......

Example of Query

With ActiveSheet.QueryTables.Add( _
* * * Connection:=Array(Array("ODBC;" & _
* * * "DSN=MS Access Database;" & _
* * * "DBQ=C:\Documents and Settings\Joel\My Documents\db1.mdb;" & _
* * * "DefaultDir=C:\Documents and Settings\Joel\My Doc"), _
* * * Array("uments;DriverId=25;" & _
* * * "FIL=MS Access;" & _
* * * "MaxBufferSize=2048;" & _
* * * "PageTimeout=5;")), _
* * * Destination:=Range("A1"))
* *.CommandText = Array( _
* * * "SELECT `Mailing List`.`Mailing ListID`," & _
* * * "`Mailing List`.FirstName," & _
* * * "`Mailing List`.MiddleName," & _
* * * "`Mailing List`.LastName" & _
* * * Chr(13) & "" & Chr(10) & _
* * * "FROM `C:\Documents and Settings\Joel\" & _
* * * "My Documents\db1`.`Mailing List` `Mailing Lis", _
* * * "t`")
* *.Name = "Query from MS Access Database"
* *.FieldNames = True
* *.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
End With
End Sub

"JumboShrimps" wrote:
Have a spreadsheet generated by idiots in Payroll.
ALL COLUMNS ARE FIXED, NOTHING
CAN BE ALTERED IN TERMS OF
WHERE DATA (FIELDS) FROM QUERY CAN BE POSITIONED.

Someone in HR enters data by hand into the spreadsheet
starting in Cell A10 to Column BW Row ??? - depends
upon how many new hires/fires previous reporting period.
Could be two rows of data, could be 22 rows of data

Have created three Access queries that return data to
the spreadsheet through MS Query (Excel 2000).
Query 1 starts in Cell A10, Query 2 in Cell A20,
and Query 3 in Cell A30.

Data is refreshed automatically every time spreadsheet
is opened, no need for HR morons to
do anything but open the spreadsheet, review the data,
send it along to Payroll, go back to playing Solitaire.
Ideal setup for any HR person.

However, the deal with the number of rows being dynamic in nature
means sometimes the new hires query returns five rows, (qry 1)
terminated employees query returns three rows (qry 2)
and changes in Title query could be ten rows (qry3).
Sometimes it's the inverse.

I need query 1 to ALWAYS return data starting in cell A10. *OK fine,
got that.
But if there are only two new hires, and query 2 is set to return the
first
row of data starting at cell A20, that means eight blank rows, and
that's bad.
Each query is a "not matching" query with different sources,
so I can't combine the three queries into one.

When the spreadsheet is sent back to payroll, there can't be any blank
rows.
OF COURSE, HR person could sort the spreadsheet,
thereby deleting blank rows,
but that would take common sense,
which is in short supply up there.

Is there any way to create some kind of array/vlookup that says?
"Query 1 has found seven new hires this reporting period,
returned seven rows of data,
Query 2 begins eight rows (cell A17) *from where query 1 started
in cell A10,
and query 3 starts the next row directly below the last row returned
in query 2"
Is that possible? *Without any involvement from end-user?
I would prefer not to use macros. *Anyting in VBA is fine.