Server based lookup table

  • Thread starter Thread starter Barry Gruver
  • Start date Start date
B

Barry Gruver

I have developed a worksheet template for creating
general ledger journal entries at my company. The first
sheet, called "JE", includes vlookup formulas to pull in
other descriptive information from a second sheet,
called "Chart of Accounts", that contains our complete
chart of accounts. It works fine, but every time a user
saves the workbook, it includes the lookup table which is
in excess of 3mb. The problem is a lot of wasted disk
space. I know one solution is to copy/paste special the
JE sheet as values only and then delete the second
sheet. However, some users are not very sophisticated
and sometimes time and pressure causes short-cutting this
procedure.

I'm not a vba programmer and not very experienced in
macros in general, but I question whether one could be
created to accomplish this easily.

Another possible solution that seems logical would be to
have the lookup table saved in a separate workbook in a
shared network folder that can be accessed by any
authorized user from within his/her own current workbook.
This would hopefully allow users to save their workbooks
without any special steps and still save space as
described above. Two questions on this scenario:

1. Is it possible to link the "lookup" workbook as
outlined?
2. Is there any inherent danger in doing this if it is
indeed possible?

If anyone has an alternative solution, I'm more than
willing to try it.

Thanks for any help you might provide.
 
#1. Try a simple test.
Create a table in one worksheet in workbook1.
In workbook2, create a bunch of =vlookup()'s that use that table.
Close the workbook with the table. You'll see the formula adjust to
include the path and folder.

(So yeah, it's possible. Sometimes preferable if you want to have lots of
control over that table--not just for size alone.)

#2. Depending on the number of =vlookup()'s, you might want to open the
workbook with the table first. Since that workbook is going to be on
a network drive, it could be slower to update. (and make your table
workbook readonly--so no one can update it and multiple people can open
their own copy.)

Alternatively, maybe you could give the user's a macro that converts to values
and deletes that giant sheet. Plop a big ole button on the worksheet and tell
them to hit it when they're done with their changes.

You could use an event to convert to values (like workbook_beforesave), but what
would happen if you did it too quickly--ah, but you could just ask each time
they save:

If you like this alternative, rightclick on the excel icon to the left of
File|Edit (on the menubar) and select view code.

Paste this in:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim resp As Long
Dim testwks As Worksheet

Set testwks = Nothing
On Error Resume Next
Set testwks = Me.Worksheets("biggiantwks")
On Error GoTo 0

If testwks Is Nothing Then
'do nothing--it's already gone
Else
resp = MsgBox(Prompt:="Convert to values and kill that giant sheet?", _
Buttons:=vbYesNo)
If resp = vbYes Then
With Me.Worksheets("input").UsedRange
.Value = .Value
End With
Application.DisplayAlerts = False
testwks.Delete
Application.DisplayAlerts = True
End If
End If

End Sub

Change the worksheet names accordingly and change the range to have the formulas
converted. I used the whole .usedrange.

Maybe you'd use something like:

With Me.Worksheets("input").range("b:e")
.Value = .Value
End With

To limit it to a few columns.
 
Back
Top