Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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



    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.

  • #2
    Re: VBA Help in migration from Older office versions to Office 2010- Excel, Word, Acc

    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.

    Comment


    • #3
      Re: VBA Help in migration from Older office versions to Office 2010- Excel, Word, Acc

      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

      Code:
      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

      Comment


      • #4
        Re: VBA Help in migration from Older office versions to Office 2010- Excel, Word, Acc

        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.

        Comment


        • #5
          Re: VBA Help in migration from Older office versions to Office 2010- Excel, Word, Acc

          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:


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

          Comment


          • #6
            Re: VBA Help in migration from Older office versions to Office 2010- Excel, Word, Acc

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


            What does this line do??




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

            Comment


            • #7
              Re: VBA Help in migration from Older office versions to Office 2010- Excel, Word, Acc

              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.

              Comment


              • #8
                Re: VBA Help in migration from Older office versions to Office 2010- Excel, Word, Acc

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

                Comment


                • #9


                  Re: VBA Help in migration from Older office versions to Office 2010- Excel, Word, Acc

                  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.

                  Code:
                  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

                  Comment

                  Working...
                  X