Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2006
    Posts
    5

    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?

    Any help would be greatly appreciated.

    Casey.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    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
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Nov 2006
    Posts
    5
    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?

  4. #4
    Join Date
    Feb 2006
    Posts
    113
    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

  5. #5
    Join Date
    Nov 2006
    Posts
    5
    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.

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091
    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.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  7. #7
    Join Date
    Feb 2006
    Posts
    113
    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

  8. #8
    Join Date
    Nov 2006
    Posts
    5
    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>

  9. #9
    Join Date
    Feb 2006
    Posts
    113
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •