PDA

View Full Version : help with copy import script



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

Norie
06-02-2005, 12:12 PM
Where do you actually want to copy the data to? An existing sheet or a new one?

By the way why are you using FileSearch when you have the name and path of the file?
Or am I missing something.

RompStar
06-02-2005, 12:43 PM
the files search I was using to find all files that I wanted imported, if I had more then 1, but not using it anymore, just left it in the code, incase I ever have to..

I want to import the data into an existing sheet, because the existing sheet will have the import button to press on to activate the macro.

Norie
06-02-2005, 12:58 PM
Which sheet to you want to import to ?

RompStar
06-02-2005, 01:42 PM
I open a Excel sheet with the macro in it, I press the button and it imports it over to the current open sheet that the VB runs from..

That local sheet is called.. ALL_test.xls and the remote sheet is also the same name.

Is that what you are refering to, because otherwise I am lost as to what you are asking..

:cloud9: