AnsweredAssumed Answered

Opening Excel Files in VBA

Question asked by Matt Hunter on Jul 17, 2017
Latest reply on Jul 18, 2017 by Ivana Kolin

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

Outcomes