locked
Updating cell and saving RRS feed

  • Question

  • Hi,

    I'm using MS Excel 2010 32 bit.

    We are have lots of spreadsheet in different folder structure and all these spreadsheets have a cell that needs to updated and saved on the same location.

    Steps performed

    a) Scan through entire folder for .xls files 

    b) If found then Open the spreadsheet

    c) Update the cell(C2) if the value is "HelloWorld"

    d) Save the changes and close the spreadsheet

    A sample code will be very helpful to kick start my project... Thanks in advance.

    Monday, July 9, 2018 4:19 PM

Answers

  • Hello StSingh,

    Please try code like

     MyFile = FileSystem.Dir(FolderLocation + @"\*.xls");
                //there is no need create the application instance every time open a workbook
                var exApp = new Microsoft.Office.Interop.Excel.Application();
                while (MyFile != "")
                {
    
                    if (MyFile.Substring(MyFile.Length - 4) == "xlsm" || MyFile.Substring(MyFile.Length - 3) == "xls")
                    {                
                        MyWorkBook = exApp.Workbooks.Open(FolderLocation + "\\" + MyFile);
                        foreach (Microsoft.Office.Interop.Excel.Worksheet MyWorkSheet in MyWorkBook.Worksheets) {
                            if (MyWorkSheet.Range["C2"].Value == "HelloWorld!")
                                MyWorkSheet.Range["C2"].Value = "NewHelloWorld!";
                        }
                        MyWorkBook.Close(SaveChanges: true);               
                    }
                }

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by StSingh Monday, July 30, 2018 1:50 PM
    Wednesday, July 11, 2018 10:13 AM

All replies

  • Hello StSingh,

    Please try something like,

    Sub OpenFiles()
    Dim MyFolder As String
    Dim MyFile As String
    Dim MyWorkBook As Workbook
    Application.ScreenUpdating = False
    MyFolder = "C:\Users\terryx\Desktop\TestFolder\TestFolder"
    MyFile = Dir(MyFolder & "\*.xls")
    Do While MyFile <> ""
    If LCase(Mid(MyFile, InStrRev(MyFile, ".") + 1)) = "xls" Then
      Set MyWorkBook = Workbooks.Open(Filename:=MyFolder & "\" & MyFile)
      If MyWorkBook.Worksheets(1).Range("C2").Value = "HelloWorld" Then
        MyWorkBook.Worksheets(1).Range("C2").Value = "NewHelloWorld!"
      End If
       MyWorkBook.Close SaveChanges:=True
    End If
    MyFile = Dir
    Loop
    Application.ScreenUpdating = True
    End Sub

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 10, 2018 2:49 AM
  • Thanks Terry.

    Should I put together a windows console application with the above code?

    Tuesday, July 10, 2018 10:27 AM
  • Hello StSingh,

    No... It is VBA code. It should be adjusted for using in  console application. Are you using VB.Net Console Application or C# Console Application?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 11, 2018 9:29 AM
  • Thanks Terry,

    I'm using C#... below is my code for your reference.

    a) Is there a way to read through all the worksheets dynamically?

    b) Other issue i am facing is while saving it doesn't override the excel file.

    public void OpenFiles()
            {
                string MyFile;
                Microsoft.Office.Interop.Excel.Workbook MyWorkBook;
                Console.WriteLine("Enter folder location:");
                string FolderLocation = Console.ReadLine();
                Console.WriteLine();
                MyFile = FileSystem.Dir(FolderLocation + @"\*.xls");
                while (MyFile != "")
                {
                    if (MyFile.Substring(MyFile.Length - 4) == "xlsm" || MyFile.Substring(MyFile.Length - 4) == "xls")
                    {
                        var exApp = new Microsoft.Office.Interop.Excel.Application();
                        MyWorkBook = exApp.Workbooks.Open(FolderLocation + "\\" + MyFile);
                        if (MyWorkBook.Worksheets[1].Range("C2").Value == "HelloWorld")
                            MyWorkBook.Worksheets[1].Range("C2").Value = "NewHelloWorld!";
                        MyWorkBook.Close(SaveChanges: true);
                    }            
                }            
            }

    Thanks for your help


    • Edited by StSingh Wednesday, July 11, 2018 10:04 AM
    Wednesday, July 11, 2018 9:54 AM
  • Hello StSingh,

    Please try code like

     MyFile = FileSystem.Dir(FolderLocation + @"\*.xls");
                //there is no need create the application instance every time open a workbook
                var exApp = new Microsoft.Office.Interop.Excel.Application();
                while (MyFile != "")
                {
    
                    if (MyFile.Substring(MyFile.Length - 4) == "xlsm" || MyFile.Substring(MyFile.Length - 3) == "xls")
                    {                
                        MyWorkBook = exApp.Workbooks.Open(FolderLocation + "\\" + MyFile);
                        foreach (Microsoft.Office.Interop.Excel.Worksheet MyWorkSheet in MyWorkBook.Worksheets) {
                            if (MyWorkSheet.Range["C2"].Value == "HelloWorld!")
                                MyWorkSheet.Range["C2"].Value = "NewHelloWorld!";
                        }
                        MyWorkBook.Close(SaveChanges: true);               
                    }
                }

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by StSingh Monday, July 30, 2018 1:50 PM
    Wednesday, July 11, 2018 10:13 AM