Address of workbook in a macro

F

frendabrenda1

I have an Excel workbook on a netwrok drive that has macros which create
record numbers and copy each record and its data to a specific worksheet in
the file. I am having problems with users copying the file or creating
shortcuts and ending up with duplicate copies of the file which pretty much
destoys the the validity of the records.

I was thinking there might be a way in the macro, rather than say
sheets("records").select I could provide the address of the correct file
like sheets(G:\\Workbook "records").select If the user were not in the
correct network drive version of the workbook, then the macro could not find
the sheet and give an error.

Is there a way to make this work, or any other ideas as to how to stop
copying of the file in any way or set up a file that can be distributed to
everyone that will only open the right file.

Any ideas would be gretly appreciate. Thank you so much!
brenda
 
D

Doug Kanter

If the user were not in the
correct network drive version of the workbook, then the macro could not
find
the sheet and give an error.
Click to expand...

Just make sure you program the exception handling, rather than just let an
"error happen". Know what I mean?
 
A

Ardus Petus

Doug is right...

Sub test()
Const WBname = "MyWorkbook.xls"
Const WSname = "records"

Dim WB As Workbook
Dim WS As Worksheet
On Error Resume Next
Set WB = Workbooks(WBname)
on error goto 0
If WB Is Nothing Then
MsgBox "Workbook " & WBname & vbLf & "is not opened"
Exit Sub
End If
on error resume next
Set WS = WB.Worksheets(WSname).Select
on error goto 0
If WB Is Nothing Then
MsgBox "Workbook " & WBname & vbLf & _
"has no sheet " & WSname
Exit Sub
End If
WB.Select
End Sub
 
F

frendabrenda1

I am not sure this answers my real problem. If a user has copied the
original file to their hard drive, then both the correct network file and the
copied hard drive file are called "MyWorkbook.xls". I am trying to find a
way that an error would occur if the user were not on the network drive
version of "MyWorkbook.xls"

Any thougths?

Thank you so much for thinking about this.

Have a GREAT day.

brenda
 
D

Doug Kanter

Brenda, are users supposed to store anything at all on their local drives?
There are a number of ways to address this issue, some from within Excel,
and some via some Windows tricks.
 
F

frendabrenda1

No, there is nothing to be stored on the user's hrd drive.

Thanks again!

brenda
 
D

Doug Kanter

In that case, you'd be better off having your network administrator prevent
this from happening via mapping and other tricks (most of which I'm not
familiar with). It was done at my home office. When users see their "My
Documents" folders, they're actually in designated folders on the server.
 
Top