Hi All,

I have a Perl program that builds an Excel sheet with embedded hyperlinks to another worksheet that runs VBA code to connect to a DB and pull details based on the active line in the hyperlink worksheet. The Perl program works great. The problem is when I click on the hyperlink. If the link goes to a location that is read/write, My Documents for example, it works great. If however the link goes to c:\ or an http site, it opens and runs the code as expected, it then prompts me to open the link again. I can click cancel and it's fine. Obviously I don't want have to click cancel every time. I do have a close at the end of the open subroutine that closes it. Not sure if that's the problem.

Any ideas?

Here is the code...

Private Sub Workbook_Open()
Dim wb1 As Excel.Workbook
Dim vendorId As String
Dim ledgerAcct As String

Set wb1 = Workbooks("xxxx.XLS")
wb1.Activate
vendorId = wb1.Sheets(1).Cells(ActiveCell.Row, 2).Value
ledgerAcct = wb1.Sheets(1).Cells(ActiveCell.Row, 3).Value

Dim SQL_String As String
Dim dbConnectStr As String

Set con = CreateObject("ADODB.Connection")
Set recset = CreateObject("ADODB.Recordset")

Dim recordCount As Long

dbConnectStr = "Provider=msdaora;Data Source=" & "xxxx;" & "User Id=xxxx" & "; Password=" & "xxxx"
con.ConnectionString = dbConnectStr
con.Open dbConnectStr

SQL_String = "select ...."
recset.Open SQL_String, con

Dim currentSheet As String
currentSheet = vendorId + " " + ledgerAcct
If ActiveWorkbook.Sheets.Count > 1 Then
Application.DisplayAlerts = False
wb1.Sheets(2).Delete
Application.DisplayAlerts = True
End If
wb1.Sheets.Add After:=Sheets(ActiveWorkbook.Sheets.Count)
wb1.Sheets(ActiveWorkbook.Sheets.Count).Activate
wb1.Sheets(ActiveWorkbook.Sheets.Count).Name = currentSheet

Dim x As Integer
wb1.Sheets(currentSheet).Cells(1, 1).Value = "VENDOR_NAME"
wb1.Sheets(currentSheet).Cells(1, 1).Value = "VENDOR_NAME"
wb1.Sheets(currentSheet).Cells(1, 2).Value = "VENDOR_NUMBER"
wb1.Sheets(currentSheet).Cells(1, 3).Value = "LEDGER_ACCOUNT"
wb1.Sheets(currentSheet).Cells(1, 4).Value = "ENTITY"
wb1.Sheets(currentSheet).Cells(1, 5).Value = "INVOICE_NUMBER"
wb1.Sheets(currentSheet).Cells(1, 6).Value = "INVOICE_DATE"
wb1.Sheets(currentSheet).Cells(1, 7).Value = "DESCRIPTION"
wb1.Sheets(currentSheet).Cells(1, 8).Value = "AMOUNT"

x = 2
Do While Not recset.EOF = True
wb1.Sheets(currentSheet).Cells(x, 1).Value = recset("VENDOR_NAME")
wb1.Sheets(currentSheet).Cells(x, 2).Value = recset("VENDOR_NUMBER")
wb1.Sheets(currentSheet).Cells(x, 3).Value = recset("LEDGER_ACCOUNT")
wb1.Sheets(currentSheet).Cells(x, 4).Value = recset("ENTITY")
wb1.Sheets(currentSheet).Cells(x, 5).Value = recset("INVOICE_NUMBER")
wb1.Sheets(currentSheet).Cells(x, 6).Value = recset("INVOICE_DATE")
wb1.Sheets(currentSheet).Cells(x, 7).Value = recset("DESCRIPTION")
wb1.Sheets(currentSheet).Cells(x, 8).Value = recset("AMOUNT")

x = x + 1
recset.MoveNext
Loop
recset.Close
con.Close
wb1.Sheets(currentSheet).Activate
wb1.Sheets(currentSheet).Columns(6).NumberFormat = "mm/dd/yyyy"
wb1.Sheets(currentSheet).Rows(1).Font.Bold = True
wb1.Sheets(currentSheet).Cells.Select
wb1.Sheets(currentSheet).Cells.EntireColumn.AutoFit
wb1.Sheets(currentSheet).Range("A1").Select

Workbooks("HL.xls").Close

End Sub