ok, here is how I have it...
It works, but I am wondering if a slight change can be made...
Currently, if I understand this right...
IF the sheet exists, then delete it and that's it... script ends..
but what I wanted is...
IF the sheet exists delete it, and then import the new one
IF it doesn't exists, import the new one...
what's the best way to adjust that ? Basically right now I press the button twice to run the script, first press deletes the existing sheet, second press imports it..
Thanks.
Option Explicit
Sub importsheet()
Dim basebook As Workbook ' local workbook that file will be merged into...
Dim mybook As Workbook ' the remote file that we need to get
Dim i As Long
' check to see if the sheet already exists from a previous import and if it
' does, delete it, then go to the next routine to import a fresh copy
If SheetExists("ALL_test.xls") = True Then
Application.DisplayAlerts = False
Sheets("ALL_test").Delete
Application.DisplayAlerts = True
Else
GoTo StartImport
' import a fresh copy
StartImport:
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.Filename = "ALL_test.xls"
.LookIn = "\\local\path\"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
Set basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
ActiveSheet.Name = mybook.Name
mybook.Close
Next i
End If
End With
Application.ScreenUpdating = True
End If
End Sub
Function SheetExists(strWSName As String, Optional wbk As Workbook) As Boolean
Dim ws As Worksheet
If wbk Is Nothing Then Set wbk = ActiveWorkbook
On Error Resume Next
Set ws = wbk.Worksheets(strWSName)
On Error GoTo 0
If Not ws Is Nothing Then
SheetExists = True
Else
SheetExists = False
End If
End Function
Thanks for helping me out, site like this are an asset to the internet, a real useful web site, and not junk.