Copy file and closing original

R

Rob

Hi,

Excel 2000

I'm using VBA to open a text file, this file opens fine but I want to avoid
anyone choosing save or save as and the original text file being over
written. I think I need to open the file and change the property to Read
Only if this is possible.

One way I thought of getting over the issue was to copy the newly opened
file, this new file is then named BOOKn.xls (where n is a number). I would
then close the original text file without saving. I have used VBA to
capture the name and full path of the original text file in a variable named
myFileName and tried using Kill myFileName to close the file, alas this
doesn't work. Also, I'm somewhat lost as to how I copy a whole workbook.

Thanks, Rob
 
R

Revolvr

Just use this when you open:

Workbooks.Open FileName:=xFile, ReadOnly:=True

and I use this when I close:

Workbooks(Fname).Close SaveChanges:=False

This will prevent any changes from being made.

-- Rev
 
R

Rob

Rev,

I'm using Workbooks.OpenText filename: = myFileName but the option to add
Read Only for a text file doesn't seem to be there.
 
D

Dave Peterson

You could change the file to readonly after you open it. Or just move the
worksheet to a new (or existing) workbook.

Option Explicit
Sub testme1()

Dim wkbk As Workbook
Dim wks As Worksheet

Workbooks.OpenText Filename:="..."

Set wkbk = ActiveWorkbook
ActiveSheet.Copy
Set wks = ActiveSheet
wkbk.Close SaveChanges:=False

End Sub

Sub testme2()

Dim wkbk As Workbook
Dim wks As Worksheet

Workbooks.OpenText Filename:="..."

Set wkbk = ActiveWorkbook
wkbk.ChangeFileAccess Mode:=xlReadOnly

End Sub
 
Top