VBA Excel RowHeight (Diff-Range)

boblarson

MrExcel MVP
Joined
Nov 14, 2008
Messages
1,964
Bob

Are you referring to the use of things like ActiveSheet, Columns etc without references?
Right,
I am talking about things like

Activesheet.Range("A1").Select

Where it should be tied to an application object ,

obXL.Activesheet.Range("A1").Select
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

imfarhan

Board Regular
Joined
Jan 29, 2010
Messages
101
Hi Bob & Norie,
sorry for late reply...
The application originally written by some one , I just maintain , i think the Ms Automation application does the followng step to run each job

Step (1)
Code:
Function CreateXLSReport(clsReport As TaskInfo) As ReturnStatus
    Dim wb As Workbook
    Dim uReturn As ReturnStatus
 
    On Error GoTo Err_Handler
    'LogUpdate "Check FS-temp-1: "
 
    uReturn = OpenXLFile(clsReport.SourceDir & clsReport.SourceFilename, wb)
 
    Select Case uReturn
        Case ReturnStatus.AllOK
            wb.Application.ScreenUpdating = False
            'Cycle through all the sheets and refresh any query tables found
            uReturn = [COLOR=darkred][B]RefreshwbQueryTables(wb)[/B][/COLOR]
Step (2)

OpenXLFile ()
HTML:
 Returns a reference to the newly opened workbook via the wbReport parameter.
Function OpenXLFile(sTemplateFile As String, wbReport As Workbook) As ReturnStatus
    On Error GoTo Err_Handler
    If Dir(sTemplateFile, vbNormal) <> "" Then
        LogUpdate "Opening Template: " & sTemplateFile
        Set wbReport = Workbooks.Open(sTemplateFile, False)
        wbReport.Application.Visible = True
 
        'Reset the Excel Addins to prevent the formula from sometimes
        ' showing #Name instead of the full report date range and run date
        On Error Resume Next
        wbReport.Application.AddIns("Analysis Toolpak").Installed = False
        wbReport.Application.AddIns("Analysis Toolpak - VBA").Installed = False
        On Error GoTo Err_Handler
 
        DoEvents
        wbReport.Application.AddIns("Analysis Toolpak").Installed = True
        DoEvents
        wbReport.Application.AddIns("Analysis Toolpak - VBA").Installed = True
        DoEvents
 
        OpenXLFile = AllOK
Step (3) which is called from Step(1) CreateXLS Report
Private Function RefreshwbQueryTables(wbReport As Workbook) As ReturnStatus
Dim qt As QueryTable
Dim shtTmp As Variant
Dim SQLTmp As String

On Error GoTo Err_Handler

For Each shtTmp In wbReport.Sheets
If InStr(1, shtTmp.Name, "CHART") = 0 Then
For Each qt In shtTmp.QueryTables

' 'Let's replace any ODBC connections with an OLEDB connection for speed and reliability(?)
' qt.Connection = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentDb.Name & ";User Id=admin;Password=;"
' qt.MaintainConnection = True

'Rebuild the query table SQL to handle any field changes in the source query/table
' SQLTmp = qt.CommandText
' SQLTmp = "SELECT * " & Right(SQLTmp, Len(SQLTmp) - (InStr(1, SQLTmp, "FROM") - 1))
' qt.CommandText = SQLTmp
' Debug.Print shtTmp.Name

'Refresh the data and wait until everything has been returned
qt.Refresh BackgroundQuery:=False

Next
End If
Next

RefreshwbQueryTables = AllOK
I think thats what Automation generate report, The queries written behing the Ms Query and source of those queries are in table
But I think you're right its not an effective way to use Ms Query as it give me loads of problem already i.e. locking and some time reports failed and so on
I need to use TranferSpreadsheet function as you advised or some other good way but don't know how?
I have around 100 reports which runs daily/weekly and monthly. (I need to find out the way to call all queries using Recordset as per their frequence to run i.e. daily/weekly or monthly... don't konw how how can plus each report have different templae (which I do'nt think I need it if I chose a new route i.e. TransferData).

This is one of my aim for this year.... to completely take off the Ms Query route..... and the new steps in mind are as follows:-
step1 - Create Queries for those reports due to run
step2 - Keep the results using Recordset
step 3 - The output will be in new excel sheet
Step4 - Save the output in new location with datetime stamp.

Please correct me and give me your assitance

Many thanks and Regards
Farhan
 
Last edited:

Forum statistics

Threads
1,089,548
Messages
5,408,898
Members
403,240
Latest member
AlenKovacevic

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top