Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

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

  1. #1
    Join Date
    18th May 2011
    Posts
    53

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th May 2011
    Posts
    53

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    18th May 2011
    Posts
    53

    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

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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    1st September 2010
    Posts
    7,916

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    18th May 2011
    Posts
    53

    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:


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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    18th May 2011
    Posts
    53

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




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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    1st September 2010
    Posts
    7,916

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    18th May 2011
    Posts
    53

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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    18th May 2011
    Posts
    53

    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.

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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 3
    Last Post: January 25th, 2011, 05:12

Bookmarks

Posting Permissions

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