After I run this code, when I open the file in which this code is contained, it opens just fine; however once I close it and open it again only the top and the bottom showup and it appears to freeze up. Help please.
Thanks
Sub SaveFile()
Set Currentsheet = Application.ActiveSheet
Set objExcel = CreateObject("Excel.Application"
Path = "Q:\Department\Cerner\Private Folders\DAILY_TAT"
objExcel.Workbooks.Open Path & "\TAT Template.xls"
Set xlsheet = objExcel.ActiveWorkbook
xlsheet.Sheets(1).Activate
With xlsheet.ActiveSheet
.Range("D6:F25"
.ClearContents
.Cells(6, 4).Value = Currentsheet.Cells(6, 4).Value
End With
strFileName = "9_5_03TAT"
bExcelFileWithOutSameName = False
Do Until bExcelFileWithOutSameName = True
On Error Resume Next
Set objCheckForDupName = GetObject(Path & "\" & strFileName & ".xls"
If Err.Number = 432 Then
bExcelFileWithOutSameName = True
Err.Clear ' Clear Err object in case error occurred.
Exit Do
Else
Err.Clear
bExcelFileWithOutSameName = False
End If
Loop
If bExcelFileWithOutSameName = True Then
xlsheet.SaveAs Path & "\" & strFileName & ".xls"
End If
MsgBox "Export Complete !!!", vbExclamation, "Export Complete"
objExcel.Workbooks.Close Path & "\TAT_Template.xls"
objExcel.Close
End Sub
Thanks
Sub SaveFile()
Set Currentsheet = Application.ActiveSheet
Set objExcel = CreateObject("Excel.Application"
Path = "Q:\Department\Cerner\Private Folders\DAILY_TAT"
objExcel.Workbooks.Open Path & "\TAT Template.xls"
Set xlsheet = objExcel.ActiveWorkbook
xlsheet.Sheets(1).Activate
With xlsheet.ActiveSheet
.Range("D6:F25"
.Cells(6, 4).Value = Currentsheet.Cells(6, 4).Value
End With
strFileName = "9_5_03TAT"
bExcelFileWithOutSameName = False
Do Until bExcelFileWithOutSameName = True
On Error Resume Next
Set objCheckForDupName = GetObject(Path & "\" & strFileName & ".xls"
If Err.Number = 432 Then
bExcelFileWithOutSameName = True
Err.Clear ' Clear Err object in case error occurred.
Exit Do
Else
Err.Clear
bExcelFileWithOutSameName = False
End If
Loop
If bExcelFileWithOutSameName = True Then
xlsheet.SaveAs Path & "\" & strFileName & ".xls"
End If
MsgBox "Export Complete !!!", vbExclamation, "Export Complete"
objExcel.Workbooks.Close Path & "\TAT_Template.xls"
objExcel.Close
End Sub