dodonohoe
07-30-2013, 07:49 AM
I have a certain excel file and when I open it (without using VBA) it gives an error message:
"Excel found unreadable content in 'EXP_Explanation_DATA_OVERVIEW_SUMMARY32036.xlsm'.
Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes."
If I click okay it opens without error. I would like to bypass the error and allow vba to open the workbook but am not sure how to use the XlRepairFile on openworkbook function. Here is the line that it is failing at, I'm not sure where to insert the XlRepairFile piece
On Error Resume Next: Set wb = Workbooks.Open(MyFolder & "\" & MyFile): On Error GoTo 0
Here is the full sub, the line of code above is marked 'HELP towards the bottom of the sub
Sub Inputs1(ByVal x As Integer)
'This sub is for the excel piece
'tells the macro that the macro is the active workbbook so that it can go_
'back to the active workbook after opening other workbooks
Dim w As Workbook
Set w = ActiveWorkbook
'dim all my variables, these are put in by the user on the main macro workbook
Dim Variablez As String
Dim MyFolder As String
Dim MyFile As String
Dim wb As Workbook
Dim FileName As String
Dim Action As String
Dim Location As String
Dim strPathNFile As String
Dim NewFile As String
Dim fileformat As String
MyFolder = Trim(Cells(x, 3))
FileName = Trim(Cells(x, 4))
Action = Trim(Cells(x, 5))
Variablez = Trim(Cells(1, 6))
Location = Trim(Cells(x, 7))
NewFile = Trim(Cells(x, 8))
fileformat = "xls"
MyFile = Dir(MyFolder & "\" & FileName & "." & fileformat)
FilePath = MyFolder & "\" & FileName
MyNewFile = Dir(Location & "\" & NewFile)
NewFilepath = Location & "\" & NewFile
Application.DisplayAlerts = False
'This piece opens the file
If WBIsOpen(MyFile) And Action = "Open" Then
'do nothing except tell the user the report is already open
Cells(x, 6) = "This report is already open"
Else
If MyFile <> "" And FileName <> "" And Action = "Open" Then
Cells(x, 6) = "This report has been opened"
'On Error Resume Next: Workbooks.Open MyFolder & "\" & MyFile: On Error GoTo 0
On Error Resume Next: Workbooks.Open MyFolder & "\" & MyFile: On Error GoTo 0
End If
End If
'this piece saves the file to file
If WBIsOpen(MyFile) And Action = "Save" Then
'do nothing except tell the user the report is already open
Cells(x, 6) = "This report is already open"
Else
If MyFile <> "" And FileName <> "" And Action = "Save" Then
Cells(x, 6) = "This report has been saved to file"
Application.DisplayAlerts = False
'HELP HELP HELP HELP HELP
On Error Resume Next: Workbooks.Open FileName:=MyFolder & "\" & MyFile: On Error GoTo 0
'save the file to your desired location
Application.DisplayAlerts = False
strPathNFile = Location & "\" & MyNewFile
ActiveWorkbook.SaveAs FileName:= _
strPathNFile, _
fileformat:=xlWorkbookNormal, _
CreateBackup:=False
ActiveWindow.Close
Application.DisplayAlerts = True
End If
End If
If WBIsOpen(MyFile) And Action = "Print" Then
Cells(x, 6) = "This report is already open please close it and try again"
Else
If MyFile <> "" And FileName <> "" And Action = "Print" Then
Cells(x, 6) = "This report has been printed and closed"
On Error Resume Next: Set wb = Workbooks.Open(MyFolder & "\" & MyFile): On Error GoTo 0
wb.Sheets.PrintOut
wb.Close True
End If
End If
w.Activate
End Sub
Thanks,
Des
"Excel found unreadable content in 'EXP_Explanation_DATA_OVERVIEW_SUMMARY32036.xlsm'.
Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes."
If I click okay it opens without error. I would like to bypass the error and allow vba to open the workbook but am not sure how to use the XlRepairFile on openworkbook function. Here is the line that it is failing at, I'm not sure where to insert the XlRepairFile piece
On Error Resume Next: Set wb = Workbooks.Open(MyFolder & "\" & MyFile): On Error GoTo 0
Here is the full sub, the line of code above is marked 'HELP towards the bottom of the sub
Sub Inputs1(ByVal x As Integer)
'This sub is for the excel piece
'tells the macro that the macro is the active workbbook so that it can go_
'back to the active workbook after opening other workbooks
Dim w As Workbook
Set w = ActiveWorkbook
'dim all my variables, these are put in by the user on the main macro workbook
Dim Variablez As String
Dim MyFolder As String
Dim MyFile As String
Dim wb As Workbook
Dim FileName As String
Dim Action As String
Dim Location As String
Dim strPathNFile As String
Dim NewFile As String
Dim fileformat As String
MyFolder = Trim(Cells(x, 3))
FileName = Trim(Cells(x, 4))
Action = Trim(Cells(x, 5))
Variablez = Trim(Cells(1, 6))
Location = Trim(Cells(x, 7))
NewFile = Trim(Cells(x, 8))
fileformat = "xls"
MyFile = Dir(MyFolder & "\" & FileName & "." & fileformat)
FilePath = MyFolder & "\" & FileName
MyNewFile = Dir(Location & "\" & NewFile)
NewFilepath = Location & "\" & NewFile
Application.DisplayAlerts = False
'This piece opens the file
If WBIsOpen(MyFile) And Action = "Open" Then
'do nothing except tell the user the report is already open
Cells(x, 6) = "This report is already open"
Else
If MyFile <> "" And FileName <> "" And Action = "Open" Then
Cells(x, 6) = "This report has been opened"
'On Error Resume Next: Workbooks.Open MyFolder & "\" & MyFile: On Error GoTo 0
On Error Resume Next: Workbooks.Open MyFolder & "\" & MyFile: On Error GoTo 0
End If
End If
'this piece saves the file to file
If WBIsOpen(MyFile) And Action = "Save" Then
'do nothing except tell the user the report is already open
Cells(x, 6) = "This report is already open"
Else
If MyFile <> "" And FileName <> "" And Action = "Save" Then
Cells(x, 6) = "This report has been saved to file"
Application.DisplayAlerts = False
'HELP HELP HELP HELP HELP
On Error Resume Next: Workbooks.Open FileName:=MyFolder & "\" & MyFile: On Error GoTo 0
'save the file to your desired location
Application.DisplayAlerts = False
strPathNFile = Location & "\" & MyNewFile
ActiveWorkbook.SaveAs FileName:= _
strPathNFile, _
fileformat:=xlWorkbookNormal, _
CreateBackup:=False
ActiveWindow.Close
Application.DisplayAlerts = True
End If
End If
If WBIsOpen(MyFile) And Action = "Print" Then
Cells(x, 6) = "This report is already open please close it and try again"
Else
If MyFile <> "" And FileName <> "" And Action = "Print" Then
Cells(x, 6) = "This report has been printed and closed"
On Error Resume Next: Set wb = Workbooks.Open(MyFolder & "\" & MyFile): On Error GoTo 0
wb.Sheets.PrintOut
wb.Close True
End If
End If
w.Activate
End Sub
Thanks,
Des