Spreadsheet - Bringing in DB information

CaseyCC · #1 (**permalink**) 11-15-06, 13:49

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?

Any help would be greatly appreciated.

Casey.

shades · #2 (**permalink**) 11-15-06, 16:21

Howdy. The second problem should be solved first, and then the first is relatively easy.

2) Always keep data on its own worksheet. The table you mentioned should be moved to a different worksheet, so that the imported data will never be affected.

1) Last row for use in VBA. This assumes you data is in columns A:F, and Col. A is the reference to check whether filled.

Code:

Sub MyLastRowAutofill()
    Range("G2").AutoFill Destination:=Range("G2:G" & Range("A" & Rows.Count).End(xlUp).Row)
    With Range("G2:G" & Range("A" & Rows.Count).End(xlUp).Row)
        .FillDown
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With

End Sub

CaseyCC · #3 (**permalink**) 11-15-06, 16:51

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.

Any ideas for that?

Fazza · #4 (**permalink**) 11-16-06, 03:57

Casey,

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.

regards,
Fazza

CaseyCC · #5 (**permalink**) 11-16-06, 11:35

That works great. Thank you very much Fazza.

One last quick question. In this case, with this spreadsheet being dependant on a number of other sheets, can I run a macro from with this sheet that would perform a refresh data on other sheets?

I know the VB for doing this in the existing sheet is Selection.QueryTable.Refresh BackgroundQuery:=False, but am not sure how to extend that to other sheets.

Thanks again.

shades · #6 (**permalink**) 11-16-06, 12:41

Quote:

Originally Posted by CaseyCC

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

Fazza provides an alternative.

Fazza · #7 (**permalink**) 11-16-06, 18:51

Quote:

Originally Posted by CaseyCC

In this case, with this spreadsheet being dependant on a number of other sheets, can I run a macro from with this sheet that would perform a refresh data on other sheets?

I know the VB for doing this in the existing sheet is

Code:

Selection.QueryTable.Refresh BackgroundQuery:=False

, but am not sure how to extend that to other sheets.

Hi Casey - you're most welcome re the previous suggestions.

Instead of Selection.QueryTable.etc, and in the normal situation of one QT on a sheet, it is normal to use either

Code:

ActiveSheet.QueryTable(1).Refresh BackgroundQuery:=False

Or, usually the sheets' code name - not the same thing as the worksheet's name as on the tab, OK? - such as

Code:

Sheet1.QueryTable(1).Refresh BackgroundQuery:=False

Normally I'd have changed the worksheets' code name from within the VBE when creating the file, so it might be something like

Code:

wks_Report.QueryTable(1).Refresh BackgroundQuery:=False

So to refresh multiple QTs in a workbook, something like,

Code:

wks_Annual_Report.QueryTable(1).Refresh BackgroundQuery:=False
wks_Monthly_Report.QueryTable(1).Refresh BackgroundQuery:=False
wks_Area_Summary.QueryTable(1).Refresh BackgroundQuery:=False

Or you can do something like this (untested)

Code:

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.

HTH
Fazza

CaseyCC · #8 (**permalink**) 11-16-06, 19:43

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

Thanks again.

Casey.
<insert catchy line here>

Fazza · #9 (**permalink**) 11-16-06, 21:51

For refreshing queries in other workbooks, I think you'd have to open the file. Changing instead to just one workbook, if it suits, sounds very good.

Internet Services

Web Development

Digital Marketing

Consumer Tech