Best Method for Implementing a Way to Add/Delete/Edit Excel files? RRS feed

  • Question

  • I'm an intern and somehow got put on this project, I'm trying to go above and beyond but only know basic C#.

    Basically I have 200+ Excel files and a lot of people that would rather look at pictures instead of files. So i'm attempting to make a program that will allow users to;

    Navigate simply through the files on a public drive.

    Open the Excel files/Edit/Add files to groups.

    What I have so far.

    The  which is just TabControl to different sections

    Then the part I'm having problems is the next menu.  It's a combobox bounded to a dataset. I have a Microsoft Access database file that has the hyperlinks to all these files. There are then three textboxes on the page that are databounded by text to the dataset.

    Is this even close to the best way to do it? Can you even open excel files and edit them in the C# Visual Studio form?

    If this is the wrong area like I said I'm very to new this and am extremely sorry. Thank you for any help.

    Friday, July 25, 2014 6:27 PM

All replies

  • Hello,

    Do you develop an add-in or standalone application?

    It looks like the question is not related to VSTO at all. That's why I'd recommend asking Excel specific questions in the Excel for Developers forum instead.

    It seems you need to develop a standalone application for managing files. See How to automate Microsoft Excel from Microsoft Visual C#.NET for more information.

    Thursday, July 31, 2014 2:10 PM
  • Hi jthom

    I agree with Eugene that this sounds more like a "standalone" application rather than a VSTO solution, which would run in-process with the Excel application. So I will move your question to the specialized Excel for Developers forum. Please note that any illustrations you are referring to in your question are not appearing in the forum, so it's a bit difficult to follow the details...

    FWIW, in answer to your question: "Can you even open excel files and edit them in the C# Visual Studio form?"

    Not really, or at least, not satisfactorily since version 2007. A Web Browser control would probably be your best bet. The other historical approach is DSOFramer, but that has real problems since the switch to the new formats and introduction of 64-bit. You can search both terms and you should turn up some discussions on how to implement these to host an Office application (most often Word and Excel).

    Cindy Meister, VSTO/Word MVP, my blog

    Sunday, August 3, 2014 7:52 AM
  • I don't think showing pictures is a good idea.  But I definitely like the idea of creating hyperlinks to files, so you can click the link and open the file.

    Please try this in Excel (it has nothing to do with C#).

    Option Explicit
    Private cnt As Long
    Private arfiles
    Private level As Long
    Sub Folders()
    Dim i As Long
    Dim sFolder As String
    Dim iStart As Long
    Dim iEnd As Long
    Dim fOutline As Boolean
        arfiles = Array()
        cnt = -1
        level = 1
        sFolder = "C:\Users\Excel\Desktop\Coding\Microsoft Excel\Work Samples\"
        ReDim arfiles(2, 0)
        If sFolder <> "" Then
            SelectFiles sFolder
            Application.DisplayAlerts = False
            On Error Resume Next
            On Error GoTo 0
            Application.DisplayAlerts = True
            Worksheets.Add.Name = "Files"
            With ActiveSheet
                For i = LBound(arfiles, 2) To UBound(arfiles, 2)
                    If arfiles(0, i) = "" Then
                        If fOutline Then
                            Rows(iStart + 1 & ":" & iEnd).Rows.Group
                        End If
                        With .Cells(i + 1, arfiles(2, i))
                            .Value = arfiles(1, i)
                            .Font.Bold = True
                        End With
                        iStart = i + 1
                        iEnd = iStart
                        fOutline = False
                        .Hyperlinks.Add Anchor:=.Cells(i + 1, arfiles(2, i)), _
                                        Address:=arfiles(0, i), _
                                        TextToDisplay:=arfiles(1, i)
                        iEnd = iEnd + 1
                        fOutline = True
                    End If
                .Columns("A:Z").ColumnWidth = 5
            End With
        End If
        'just in case there is another set to group
        If fOutline Then
            Rows(iStart + 1 & ":" & iEnd).Rows.Group
        End If
        Columns("A:Z").ColumnWidth = 5
        ActiveSheet.Outline.ShowLevels RowLevels:=1
        ActiveWindow.DisplayGridlines = False
    End Sub
    Sub SelectFiles(Optional sPath As String)
    Static FSO As Object
    Dim oSubFolder As Object
    Dim oFolder As Object
    Dim oFile As Object
    Dim oFiles As Object
    Dim arPath
        If FSO Is Nothing Then
            Set FSO = CreateObject("Scripting.FileSystemObject")
        End If
        If sPath = "" Then
            sPath = CurDir
        End If
        arPath = Split(sPath, "\")
        cnt = cnt + 1
        ReDim Preserve arfiles(2, cnt)
        arfiles(0, cnt) = ""
        arfiles(1, cnt) = arPath(level - 1)
        arfiles(2, cnt) = level
        Set oFolder = FSO.GetFolder(sPath)
        Set oFiles = oFolder.Files
        For Each oFile In oFiles
            cnt = cnt + 1
            ReDim Preserve arfiles(2, cnt)
            arfiles(0, cnt) = oFolder.Path & "\" & oFile.Name
            arfiles(1, cnt) = oFile.Name
            arfiles(2, cnt) = level + 1
        Next oFile
        level = level + 1
        For Each oSubFolder In oFolder.Subfolders
            SelectFiles oSubFolder.Path
        level = level - 1
    End Sub

    You can try this as well.

    Option Explicit
    Sub FileListingAllFolder()
    Dim pPath As String
    Dim FlNm As Variant
    Dim ListFNm As New Collection ' create a collection of filenames
    Dim OWb As Workbook
    Dim ShtCnt As Integer
    Dim Sht As Integer
    Dim MWb As Workbook
    Dim MWs As Worksheet
    Dim i As Integer
    ' Open folder selection
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        pPath = .SelectedItems(1)
    End With
    Application.WindowState = xlMinimized
    Application.ScreenUpdating = False
    ' Create master workbook with single sheets
    Set MWb = Workbooks.Add(1)
    MWb.Sheets(1).Name = "Result"
    Set MWs = MWb.Sheets("Result")
    Cells(1, 1) = "No."
    Cells(1, 2) = "Sheet Name"
    Cells(1, 3) = "File Name"
    Cells(1, 4) = "Link"
    i = 2
    ' Filling a collection of filenames (search Excel files including subdirectories)
    Call FlSrch(ListFNm, pPath, "*.xls", True)
    ' Print list to immediate debug window and as a message window
    For Each FlNm In ListFNm ' cycle for list(collection) processing
        'Start Processing here
        Set OWb = Workbooks.Open(FlNm)
        ShtCnt = ActiveWorkbook.Sheets.Count
        For Sht = 1 To ShtCnt
            MWs.Cells(i, 1) = i - 1
            MWs.Cells(i, 2) = Sheets(Sht).Name
            MWs.Cells(i, 3) = OWb.Name
            MWs.Cells(i, 4).Formula = "=HYPERLINK(""" & FlNm & """,""Click Here"")"
            i = i + 1
        Next Sht
        'End file processing file
        OWb.Close False
    Next FlNm
    ' Print to immediate debug window and message if no file was found
    If ListFNm.Count = 0 Then
        Debug.Print "No file was found !"
        MsgBox "No file was found !"
        MWb.Close False
    End If
    Application.ScreenUpdating = True
    Application.WindowState = xlMaximized
    MsgBox "You Click Cancel, and no folder selected!"
    End Sub
    Private Sub FlSrch(pFnd As Collection, pPath As String, pMask As String, pSbDir As Boolean)
    Dim flDir As String
    Dim CldItm As Variant
    Dim sCldItm As New Collection
    ' Add backslash at the end of path if not present
    pPath = Trim(pPath)
    If Right(pPath, 1) <> "\" Then pPath = pPath & "\"
    ' Searching files accordant with mask
    flDir = Dir(pPath & pMask)
        Do While flDir <> ""
            pFnd.Add pPath & flDir 'add file name to list(collection)
            flDir = Dir ' next file
    ' Procedure exiting if searching in subdirectories isn't enabled
    If Not pSbDir Then Exit Sub
    ' Searching for subdirectories in path
    flDir = Dir(pPath & "*", vbDirectory)
        Do While flDir <> ""
            ' Add subdirectory to local list(collection) of subdirectories in path
            If flDir <> "." And flDir <> ".." Then If ((GetAttr(pPath & flDir) And _
            vbDirectory) = 16) Then sCldItm.Add pPath & flDir
            flDir = Dir 'next file
    ' Subdirectories list(collection) processing
    For Each CldItm In sCldItm
        Call FlSrch(pFnd, CStr(CldItm), pMask, pSbDir) ' Recursive procedure call
    End Sub

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, August 5, 2014 10:07 PM