Hi.

I am having difficulty in figuring this out. I have an excel file that
people are going to key in a part number. I created several functions that
are going to be saved within the workbook that are going to query an access
database for information based on that part number (i.e., cost, description,
vendor, etc.). I had a sample data file that I saved as a CSV file, and I
link an access table to the file. Everything was working okay. I could
change the value in the CSV file, and it would automatic update in Excel.
Now that I received the full data file (has more parts, has the
descriptions, etc), the excel file doesn't update when loaded, and I get an
intermittent windows asking me to select a data source (just hit cancel
until it goes away, and then the values populate). The new data file is 5.1
meg (sample was 1.7 meg), but I had to go with a pipe ("|") delimited file
since there were comas in the description (I did change the pipes to comas
to see if there was a difference, but it still had the same effect). If I
set Application.volitile, there is a lag time that would not be suitable
(same as with the sample data file). My code is below. Since the data file
location may change, I created an ini file on the server that contains the
path and filename of the access database (variables path and pathlink). On
open, it reads the ini file, and I originally had application.calculate
which worked fine. For some reason, I have to use the calculatefull now.
Right now, the main issue is the window asking for the user to select the
datasource. Why am I getting that? I didn't try to import the data into
access (no link), and I would prefer not to since the file is being created
on a Unix server and FTPed to the working directory.

Thanks in advance for any help,
Brian


Dim pathlink As String
Dim path As String
Dim varalerts As String
Private Sub auto_open()
On Error Resume Next
Open "\\server\share\RouterLink\RouterLink.ini" For Input As #1
Input #1, pathlink
Input #1, path
Input #1, varalerts
Close #1
If pathlink = "" Or path = "" Then
MsgBox "Error loading RouterLink.ini file"
Exit Sub
End If
If Right$(pathlink, 1) <> "\" Then pathlink = pathlink & "\"
varalerts = LCase(varalerts)
Application.CalculateFull
'Application.Calculate
End Sub

Function proddesc(varcell)
If varcell = "" Or IsEmpty(varcell) Or IsNull(varcell) Then Exit Function
Dim Db As Database
Dim Rs As Recordset
Dim varquery As String
'Application.Volatile
varquery = "SELECT ProductNumber, Description from Routprod1 where
ProductNumber = '" & varcell & "'"
Set Db = Workspaces(0).OpenDatabase(path, ReadOnly:=True)
Set Rs = Db.OpenRecordset(varquery)
If Not Rs.EOF Then
proddesc = Rs.Fields("Description").Value
Else
proddesc = "Part Number: " & varcell & " was not found"
End If
Rs.Close
Db.Close
End Function