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
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
ErrNo = Err
On Error GoTo 0
Select Case ErrNo
Set xlWkbk = xlApp.Workbooks.Open(ExcelFilePath)
'Scenario 3 occurs and I can't get this to work)
Set xlWkbk = xlApp.Workbooks.Open(ExcelFilePath, True, False)
'Set xlWkbk = xlApp.Workbooks.Open(ExcelFilePath)
Set XMLfile = fso.CreateTextFile(ExcelFilePath, True)
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
xlSheet.Cells(j, 1).Value = "Next line"
'Also I can't figure out how to save this file...
Set xlWkbk = xlApp.Workbooks.Save(ExcelFilePath)