VBA Help in migration from Older office versions to Office 2010- Excel, Word, Access

[Rinaldo]

Hello Members,

I am into a big migration work for sometime where I have to migrate more than 200 applications from older office versions to Microsoft Office 2010. So I would post all my doubts i encounter during my migration. I would be grateful if some experts who are experienced in this can subscribe to this Thread and clear my doubts.

Kindly help me in making this easier.

[Rinaldo]

MODULE: Module Name
FUNCTION:Function Name
LINE: 111
TYPE: REMOVED
ITEM: [mso]ODSOFilter.Column
URL:http://go.microsoft.com/fwlink/?LinkID=215358
CODE: Somename = sheets("references").range("somerange").column


Could some one tell me what could be the change need to be made for the macro to work with the above report.

[Rinaldo]

Could anyone please help me in sorting why the below code does not run in Office 2010. It gives an error "Subtotal Method of Range Class failed" and the line of error is,

Selection.Subtotal GroupBy:=9, Function:=xlSum, TotalList:=Array(12), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

 
Sub Macro() 
     '
     ' '
    Columns("L:L").Select 
    Selection.Insert Shift:=xlToRight 
    Range("L1").Select 
    ActiveCell.FormulaR1C1 = "Name" 
    Range("L2").Select 
    ActiveCell.FormulaR1C1 = "=IF(RC[-5]=""s"",-RC[-1],RC[-1])" 
    Selection.AutoFill Destination:=Range("L2:L2980") 
    Range("L2:L2980").Select 
    Columns("K:L").Select 
    Selection.Style = "Comma" 
    Range("I7").Select 
    Range("A1:T2980").Sort Key1:=Range("I7"), Order1:=xlAscending, Header:= _ 
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
    Selection.Subtotal GroupBy:=9, Function:=xlSum, TotalList:=Array(12), _ 
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True 
End Sub 

[cytop]

As far as your first post goes - the link refers to a page on Microsoft.com that lists the changes in verious versions. Obviously something in the code has been removed/changed. It's a big list so I'm not going to trawl throught it. Did you?

Second post? Again, something has changed. Record a macro to add a sub-total in the new version of Excel and compare what gets generated to your existing code.

[Rinaldo]

I changed the code as follows... But it does not gives me and error.. But it executes to add hypens in the L row untill the range specified.. But i am really puzzled without knowing its functionality.


Changed Code:

 
 
With Range("A1:T2980") 
    .Subtotal GroupBy:=9, Function:=xlSum, TotalList:=Array(12), _ 
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True 
End With 

Could you please see the first post of mine and explain me what the code does by seeing the code.. Because i am not an expert in knowing from the code.. Any help would be very great for me.....

[Rinaldo]

Could you please explain me the following, "ActiveCell.FormulaR1C1 = "=RC[-5]-RC[-1]""


What does this line do??

 
 
Range("I2").Select 
ActiveCell.FormulaR1C1 = "=RC[-5]-RC[-1]" 

[cytop]

It inserts a formula that subtracts the value in the cell 1 column to the left from the value in the cell 5 columns to the left. For example, as Cell I2 is selected, the formula will be:

=D2-H2

If Cell F3 is selected, the same code will result in the formula

=A3-E3

As far as your other posts go, I can't really help any more based on the information you supplpied. You need to read and understand what's on that linked page on the Microsoft site and I don't understand your reply to the second issue.

[Rinaldo]

Thank you very much for the explanation. Very nice and clear..

Other posts... I would read the Microsoft site as you mentioned. Would keep you posted on my further doubts..

[Rinaldo]

I am pasting a code block below.. Which uses some iteration withing Application.Filesearch. Could you please edit the code for me to make it work in 2010. I hope Application. Filesearch is no longer valid in Office 2010. Could you please re-edit with some explanations to understand.

 
 
Private Sub FichImporte_GotFocus()Dim CSql As StringDim rst As New ADODB.RecordsetDim rs1 As New ADODB.RecordsetDim intLenRep As IntegerDim intLen As IntegerSet cnn = CurrentProject.ConnectionintLenRep = Len(stRepCh)    cnn.Execute "DELETE FROM FICHIERS_TROUVES"    Set rst = New ADODB.Recordset    Set rs1 = New ADODB.Recordset 'Active la connection de la base Access    Set rst.ActiveConnection = cnn    CSql = "FICHIERS_TROUVES;"    'Ouvre le recordset qui correspond à la table ci-dessous.    rst.Open CSql, , adOpenKeyset, adLockOptimistic    With Application.FileSearch        'Débute une nouvelle recherche        .NewSearch        'Définit les critères de recherche        .LookIn = stRepCh        .FileName = "*.*"        .SearchSubFolders = True    End WithWith Application.FileSearch    'Exécute la recherche        If .Execute() > 0 Then            For i = 1 To .FoundFiles.Count                'TextBox.Value = .FoundFiles(i)                intLen = Len(.FoundFiles(i))                stFichier = Right(.FoundFiles(i), intLen - intLenRep)                'Call ShowFileInfo(.FoundFiles(i))                With rst                    If Right(stFichier, 11) <> "_traite.xls" Then                        .AddNew                            .Fields(1) = stFichier                            '.Fields(2) = s                        .Update                    End If                End With                                            Next i        End IfEnd With        FichImporte.RequeryEnd Sub