none
Unable to access xlsm files in Office 365 RRS feed

  • Question

  • Hi

    We are facing some issue in accessing *.xlsm files, we have around 250 CSV files where we are loading those csv in excel by using macros and in the same page we have macros enabled to calculate values, colour, links etc. This file was working fine with my previous version of MS Office 2007 but now we got migrated to Office 365 in this we can’t normally access *.xlsm file it is getting hanged or going to not responding mode or taking too long time to process but we when checked with other excel files it works fine.  Is there any way to access this file normally or i need to change any settings in my Excel to access *.xlsm file without any issue in Office 365?


    Mohan

    Tuesday, November 3, 2015 4:25 AM

All replies

  • Hi

    We are facing some issue in accessing *.xlsm files, we have around 250 CSV files where we are loading those csv in excel by using macros and in the same page we have macros enabled to calculate values, colour, links etc. This file was working fine with my previous version of MS Office 2007 but now we got migrated to Office 365 in this we can’t normally access *.xlsm file it is getting hanged or going to not responding mode or taking too long time to process but we when checked with other excel files it works fine.  Is there any way to access this file normally or i need to change any settings in my Excel to access *.xlsm file without any issue in Office 365?


    Mohan

    Monday, November 2, 2015 12:25 PM
  • Hi,

    According your description, my understanding is that you have the following scenario:

    You are using macros of Excel xlsm file to load the CSV files as data source. This worked well in Excel 2007, but now it not works with Excel 2013/2016. Please let me know your Office version that you used Office 365.

    Was there any error message? Based on the above hypothetical scenario, I suppose that this issue might by be the macro compatibility issue between Excel 2007 and Excel 2013/2016. You'd better post a sample code here and if you want to debug it, please post the question to MSDN forum:

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=Exceldev

    Regards,

    George Zhao
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Tuesday, November 3, 2015 1:45 AM
  • Hi George,

    Thanks for your reply

    We are not getting any error message while accessing, it is taking too long time or going to not responding mode and below is the one sample code we use

    --------------------------------------------------

                       

    Sub LoadCsvFiles()

    strPath = ActiveWorkbook.Path & "\"


    'Name strFile As strNewFile

    'Load all csv files


    strFile = Dir(strPath & "*.csv")
    Do While strFile <> ""
    With ActiveWorkbook.Worksheets.Add
    With .QueryTables.Add(Connection:="TEXT;" & strPath & strFile, _
    Destination:=.Range("A1"))
    .Parent.Name = Replace(strFile, ".csv", "")
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False

    If strFile = "zzIssTestReport.csv" Then GoTo Skip1
    If strFile = "zzzDegradeList.csv" Then GoTo Skip1
    If strFile = "zzzzzIssTReport.csv" Then GoTo Skip1
    If strFile = "zzzzExecutionStatus.csv" Then GoTo Skip1
    If strFile = "zzzzzzIssComReport.csv" Then GoTo Skip1

    Range("A1").Select
       ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
           "zzIssTestReport!A1", TextToDisplay:="HOME"



    'ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        ActiveWindow.DisplayZeros = False

      ' Insert four empty rows

        Rows("2:2").Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Rows("2:4").Select
        Selection.NumberFormat = "0"

     ' Count Greens

        Range("B2").Select
        ActiveCell.FormulaR1C1 = "=COUNTIF(R[4]C:R[1004]C, ""2"")"
        Range("B2").Select
        Selection.AutoFill Destination:=Range("B2:AZ2"), Type:=xlFillDefault
        Range("B2:AZ2").Select
        Range("C2").Activate

     ' Count Yellows

        Range("B3").Select
        ActiveCell.FormulaR1C1 = "=COUNTIF(R[3]C:R[1003]C, ""3"")"
        Range("B3").Select
        Selection.AutoFill Destination:=Range("B3:AZ3"), Type:=xlFillDefault
        Range("B3:AZ3").Select
        Range("C2").Activate

     ' Count Reds

        Range("B4").Select
        ActiveCell.FormulaR1C1 = "=COUNTIF(R[2]C:R[1002]C, ""1"")"
        Range("B4").Select
        Selection.AutoFill Destination:=Range("B4:AZ4"), Type:=xlFillDefault
        Range("B4:AZ4").Select
        Range("C4").Activate

     ColorCells
     ColorThreeRows
     RotateFirstRow
     Cells.Columns.AutoFit

    Skip1:

    End With
    End With
    strFile = Dir
    Loop



     CreateLinks
     FindSum
     Line_Chart
     'modulewise_charts
    'CreateSheetNames
    'CreateSizingSheetNames
    colourCells
    AddNewColor
    Addlink
    AddComNewColor
    Addcomlink

    Sheets("zzIssTestReport").Select

    End Sub 

    ----------------------------------------------------



    Mohan

    Tuesday, November 3, 2015 6:05 AM
  • Hi,

    Sorry, I'm not familiar with VBA code, I'll move your thread to Excel DEV forum. They'll help you check the code.

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=Exceldev

    Regards,

    George Zhao
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Wednesday, November 4, 2015 1:20 AM
  • Hi Mohan,

    Can you reproduce this issue in a new xlsm file with a simple macro? If yes, it seem the issue is relative to Excel application, please try to repair and update to see whether the issue was fixed.

    If not, I suggest that you open *.xlsm files in safe mode and check the code in the workbook open event to see whether line of code causing this issue. We can copy the code in that event and create a sub for it and debug the code line by line.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, November 4, 2015 1:26 AM
    Moderator