If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Spreadsheet - Bringing in DB information

I have a spreadsheet that my company uses to indicate branch losses. When opening this sheet, the first thing I do is to refresh data. This pulls in information from a Sequel server and refreshes the cells accordingly.

The information that is refreshed is done in 7 columns.

In an effort to automate this as much as possible, I need to do two things.

In column 8, there is information that I have to manually 'fill down' to the last entry. Is there a way that I can make the fill-down be automated, based on the row number in which the last piece of refreshed data resides?

The second thing is that below the refreshed data is a table containing various pieces of information. This table is wider than the 7 columns worth of data being refreshed. Every time I perform the refresh, I have to manually take the part of the table that did not move down and move it so that it is in line with the rest of the table. Any way to automate this, or should I just put the table so that it isn't in line with the refreshed data?

Rich, thanks for the information. Taking the table out of this spreadsheet will require to me to rework what I have, but what you're saying definitely makes sense.

When I run the script that you have provided, it isn't preserving the formulas in the cells that I have - a concatenation formula. Instead, it is replacing the formulas with straight text, so that any further fill-downs will not necessarily have the correct information.

If that filled down field is a formula on the RHS of a query table being refreshed, there is an automatic setting to have it fill down to suit the range.

You can right click from within the returned range, OR, use the "Data Range Properties" on the "External Data" toolbar, to get to the properties. Set "Fill down formulas in columns adjacent to data". It will fill down adjacent/contiguous columns on the RHS automatically. Or you can set in in VBA, of course. Once set, it will stay set until you explicitly change it.

BTW please note, if you purely had text, such as "This Text", it needs to be a formula. So make it ="This Text" in the cell.

When I run the script that you have provided, it isn't preserving the formulas in the cells that I have - a concatenation formula. Instead, it is replacing the formulas with straight text, so that any further fill-downs will not necessarily have the correct information.

Any ideas for that?

The code is using the formula and then changing to values (may help reduce overhead in the workbook).

Sub test()
Dim wks As Worksheet
Dim qt As QueryTable
For Each wks In Worksheets
For Each qt In wks.QueryTables
qt.Refresh BackgroundQuery:=False
Next qt
Next wks
Set qt = Nothing
Set wks = Nothing
End Sub

There is a RefreshAll command to do update all queries in a workbook at once (please refer VBA help), but I don't use it. You may not have the desired results if one QT depends on another and the RefreshAll doesn't update them in the order you want! Same for untested code sample just above.

Thanks again. I suppose I should have clarified though. If I wanted to perform that refresh on different spreadsheets (i.e. the one I am working on is in s:\Operations1.xls, but I want to perform simultaneous refreshes on s:\Operations2.xls, s:\Operations3.xls, etc).

If that can't be done, the code you provided before will work if I combine all the branches onto one spreadsheet (which is definitely feasible).