praveenpatel421983
New Member
- Joined
- Aug 17, 2017
- Messages
- 41
Hi all,
About my excel knowledge, I am using excel vba for last 2 years. I am not an expert but I know the basics of the vba.
I am working on excel vba to extract data from other files in a particular folder and subfolders which is executing perfectly. I have used userform to select the template file as well as to select the source folder where other files are located and the program is executed using a command button called “ProceedButton”
I am using a excel template where all the extracted data will be pasted. If this file is open I get an error. To avoid that, I inserted the below code in my code. I have used workbooks.open() to set the workbook for variable but this opens the file as read only if it is already open and when I use .close, it closes the read only file not the actual file. I tried using workbooks() to set it as variable but it gives ‘runtime error 9’. I have tried various options by going through different posts by user but I was not able to find the right solution. Could someone help?
Private Sub ProceedButton_Click()
Dim FSO As Object
Dim wbk As Workbook
Dim TempPath As String
'Dim TestingForClose As Excel.Workbook
'Set TestingForClose = Workbooks(TemplateFileNameTextBox.Text) '<======= Used this text box to assisgn the file name
Application.DisplayAlerts = False
TempPath = TemplateTextBox.Text '<======= This textbox has the path of the file
If IsFileOpen(TempPath) Then
Set wbk = Workbooks.Open(TempPath) '<======= Opens read only file if the file is already open
' Set wbk = Workbooks(TempPath) '<======= Gives Runtime error 9 "Subscript out of range"
' Set wbk = Workbooks(TemplateFileNameTextBox.Text) '<======= Gives Runtime error 9 "Subscript out of range"
If MsgBox("Template file already in use!" & vbNewLine & "Do you want to close it?", vbYesNo + vbQuestion, "File in use") = vbYes Then
' wbk.Activate
' ActiveWorkbook.Close False
' wbk.Save
wbk.Close True '<======= Closes the read only file not the actual file
' If Application.ActiveWorkbook.Name = TestingForClose Then
' TestingForClose.Close SaveChanges:=True
' End If
' Workbooks(TempPath).Close savechanges:=False
Else
Exit Sub
End If
End If
End Sub
Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer
On Error Resume Next
filenum = FreeFile()
Open filename For Input Lock Read As #filenum
Close filenum
errnum = Err
On Error GoTo 0
Select Case errnum
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select
End Function
About my excel knowledge, I am using excel vba for last 2 years. I am not an expert but I know the basics of the vba.
I am working on excel vba to extract data from other files in a particular folder and subfolders which is executing perfectly. I have used userform to select the template file as well as to select the source folder where other files are located and the program is executed using a command button called “ProceedButton”
I am using a excel template where all the extracted data will be pasted. If this file is open I get an error. To avoid that, I inserted the below code in my code. I have used workbooks.open() to set the workbook for variable but this opens the file as read only if it is already open and when I use .close, it closes the read only file not the actual file. I tried using workbooks() to set it as variable but it gives ‘runtime error 9’. I have tried various options by going through different posts by user but I was not able to find the right solution. Could someone help?
Private Sub ProceedButton_Click()
Dim FSO As Object
Dim wbk As Workbook
Dim TempPath As String
'Dim TestingForClose As Excel.Workbook
'Set TestingForClose = Workbooks(TemplateFileNameTextBox.Text) '<======= Used this text box to assisgn the file name
Application.DisplayAlerts = False
TempPath = TemplateTextBox.Text '<======= This textbox has the path of the file
If IsFileOpen(TempPath) Then
Set wbk = Workbooks.Open(TempPath) '<======= Opens read only file if the file is already open
' Set wbk = Workbooks(TempPath) '<======= Gives Runtime error 9 "Subscript out of range"
' Set wbk = Workbooks(TemplateFileNameTextBox.Text) '<======= Gives Runtime error 9 "Subscript out of range"
If MsgBox("Template file already in use!" & vbNewLine & "Do you want to close it?", vbYesNo + vbQuestion, "File in use") = vbYes Then
' wbk.Activate
' ActiveWorkbook.Close False
' wbk.Save
wbk.Close True '<======= Closes the read only file not the actual file
' If Application.ActiveWorkbook.Name = TestingForClose Then
' TestingForClose.Close SaveChanges:=True
' End If
' Workbooks(TempPath).Close savechanges:=False
Else
Exit Sub
End If
End If
End Sub
Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer
On Error Resume Next
filenum = FreeFile()
Open filename For Input Lock Read As #filenum
Close filenum
errnum = Err
On Error GoTo 0
Select Case errnum
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select
End Function