2 Replies Latest reply on Jul 18, 2017 2:30 AM by Ivana Kolin

    Opening Excel Files in VBA

    Matt Hunter

      Hi! I want to be able to access a certain Excel file saved on each of our computer desktops, called Errors.

      There are 3 scenarios that I need to account for:

      1. The file does not exist and needs to be created

      2. The file exists and needs to be open

      3. The file exists and is already open

       

      I have accounted for 1 and 2. The problem is the macro, when run through this function multiple times creates several copies of the Errors document, which is very annoying. When scenario 3 happens, I want the macro to recognize that the current open excel document is the one it needs to add additional information to.

       

      Also, what is the code for saving a file? Code is below: 

       

      Dim swApp As Object
      Sub main()
      Dim ExcelFilePath As String
      Dim ExcelFile As Object
      Dim xlApp As Object
      Dim xlWkbk As Object
      'Dim xlWkbk As Excel.Workbook
      Dim xlSheet As Object
      Dim fso As Object
      Dim UserName As String
      Dim ff As Long, ErrNo As Long

       

      'Need to determine how to get the right username here
      Set swApp = Application.SldWorks
      UserName = Environ("USERNAME")
      ExcelFilePath = "C:\Users\" & UserName & "\Desktop\Errors.xlsx"
      Set fso = CreateObject("Scripting.FileSystemObject")
      Set xlApp = CreateObject("Excel.Application")

      If fso.fileexists(ExcelFilePath) Then
          On Error Resume Next
              ff = FreeFile()
              Open ExcelFilePath For Input Lock Read As #ff
              Close ff
              ErrNo = Err
              On Error GoTo 0
             
              Select Case ErrNo
              Case 0
                  'Scenario 2

                     Set xlWkbk = xlApp.Workbooks.Open(ExcelFilePath)
              Case 70
                  'Scenario 3 occurs and I can't get this to work)

                     Set xlWkbk = xlApp.Workbooks.Open(ExcelFilePath, True, False)           
              End Select
             
          'Set xlWkbk = xlApp.Workbooks.Open(ExcelFilePath)
      Else
          'Scenario 1

           Set XMLfile = fso.CreateTextFile(ExcelFilePath, True)
      End If
      xlApp.Visible = True
      Set xlSheet = xlWkbk.Worksheets(1)
      xlSheet.Cells(1, 1).Value = "Part #"
      xlSheet.Cells(1, 2).Value = "Error"
      For j = 2 To 100
          If xlSheet.Cells(j, 1).Value = "" Then Exit For

      Next j
      xlSheet.Cells(j, 1).Value = "Next line"

       

      'Also I can't figure out how to save this file...
      Set xlWkbk = xlApp.Workbooks.Save(ExcelFilePath)
      End Sub

        • Re: Opening Excel Files in VBA
          Josh Brady

          google "createobject" vs "getobject". 

           

          then you need to iterate through excel's open documents.  Google helps you there too.

          • Re: Opening Excel Files in VBA
            Ivana Kolin

            to find desktop folder you can better use this:

            Public Function SpecialFolderPath(strFolder As String) As String
                ' Find out the path to the passed special folder. User on of the following arguments:
                ' Options For specical folders
            '        AllUsersDesktop
            '        AllUsersStartMenu
            '        AllUsersPrograms
            '        AllUsersStartup
            '        Desktop
            '        Favorites
            '        Fonts
            '        MyDocuments
            '        NetHood
            '        PrintHood
            '        Programs
            '        Recent
            '        SendTo
            '        StartMenu
            '        Startup
            '        Templates
             
               On Error GoTo ErrorHandler
             
               'Create a Windows Script Host Object
                  Dim objWSHShell As Object
                  Set objWSHShell = CreateObject("WScript.Shell")
             
               'Retrieve path
                  SpecialFolderPath = objWSHShell.SpecialFolders(strFolder & "")
             
               ' Clean up
                  Set objWSHShell = Nothing
                  Exit Function
             
            '**************************************
            '*      Error Handler
            '**************************************
            ErrorHandler:
                MsgBox "Error finding " & strFolder, vbCritical + vbOKOnly, "Error"
            End Function
            
            

             

            Instead of excel I would use comma separated file. It is easier and faster.