Reading and deleting a selected sheet!

  • Thread starter Thread starter aiyer
  • Start date Start date
A

aiyer

Hello all!

A quick question.
I was trying to provide a browzer window that helps the user to selec
a particular type of file (say x.scompfinal) from a folder and the
read it into an existing worksheet in the workbook, called 'vtec.xls'
I used the following command for the above task and it works.

myfile = Application.GetOpenFilename("scompfinal files,*.scompfinal")

But, after reading the above file, I would like to delete it.
and I am not able to select the sheet with the name "myfile".
It is giving me errors. I am atatching the Excel macro that I wrote fo
the same for your ref:
Thanks alot & would appreciate your help guys.

Arun....
Vt Corp

Attachment filename: sample.txt
Download attachment: http://www.excelforum.com/attachment.php?postid=47997
 
To delete the file:

'Delete the file we're attaching
Kill myFile

To open the workbook:

Set myWkb = Workbooks.Open(myFile)


To select the worksheet:

myWkb.Worksheets("myfile").Activate

Are you sure the name of the sheet is myfile? I thought that was the name of
the workbook you're trying to open.
--
_______________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel
http://www.r-cor.com
 
Robert:

Thanks a bunch. Nope "myfile" is the file that was browzed and selecte
by the user and was read-in. So after copying contents from thi
read-in file into the existing 'vtec.xls' sheet in the workbook, I'l
have to delete it.
So there is no need to open a workbook necessarily, as the workbook i
already open.
Do you want me to make any changes or shall I ry out the one you wrot
me back?

Thanks again.
Regds,

Arun.
Vtec Corp
 
aiyer

Correct me if i am wrong

Having looked at your code I beleive what you want your macro to do i
to:-
open a file
copy column a & b and pastes it into a sheet in another workbook

if this is correct then your existing code does a lot of unnessar
work


try my code
If you wish to delete the file you opened then add the command
kill myfile
before End Sub


Sub dddd()
Dim MyFile As String
Dim Wb As Workbook
Dim wsFrom As Worksheet
Dim wsTo As Worksheet

Application.DisplayAlerts = False

Application.DisplayAlerts = False
MyFile$ = Application.GetOpenFilename("scompfinal files,*.scompfinal")
Workbooks.OpenText FileName:=MyFile, _
Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(7, 1), Array(21, 1), Array(42, 1))

Set wsFrom = ActiveSheet
Set wsTo = Workbooks("book1").Sheets("vtec"
'Workbooks("vtec.xls").Sheets("vtec")

wsFrom.Columns("A:B").Copy Destination:=wsTo.Columns("f:g")
Application.CutCopyMode = False
ActiveWorkbook.Close SaveChanges:=False
End Su
 
Back
Top