RompStar
06-02-2005, 09:47 AM
This script is simple, when played it imports an exact copy of the file into the open workbook...
The problem that I am having is that I installed a button on the sheet for this script, when I play it, the script imports that sheet, and then the button it's self is gone, because the old sheet was replaced with the import...
Since there are always only 4 columns in the sheet that I import over the network, A, B, C, D, how hard would it be to select only those columns to the last used row and import that over ?
so if I install the button in columns 6 or something, it wouldn't go away after the import. :think:
thanks for the help ahead of time.
Option Explicit
Sub ImportWorks()
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
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.Filename = "ALL_test.xls"
.LookIn = "\\local\network"
.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 Sub
The problem that I am having is that I installed a button on the sheet for this script, when I play it, the script imports that sheet, and then the button it's self is gone, because the old sheet was replaced with the import...
Since there are always only 4 columns in the sheet that I import over the network, A, B, C, D, how hard would it be to select only those columns to the last used row and import that over ?
so if I install the button in columns 6 or something, it wouldn't go away after the import. :think:
thanks for the help ahead of time.
Option Explicit
Sub ImportWorks()
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
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.Filename = "ALL_test.xls"
.LookIn = "\\local\network"
.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 Sub