Hello Everyone,

I used the Import external data function to bring a single table from my
MSSQL database into a spreadsheet as a tab of the sheet. When I open the
workbook by clicking on it or by going to it in the list of recent files
everything opens correctly. A pop up window asks me if I want to update the
data or not. Clicking yes populates the tab with the table and clicking no
leaves the tab blank.

Now then, I am using VB to open the spreadsheet from another program. I have
placed a button on the form in that sheet. The workbook must be opened as an
object. When I open from there I am not asked if I want to update the way
that I am asked by opening otherwise. Below is the code that open the
workbook from our MRP program.


Private Function OpenQUOTEWorksheet() As Boolean
On Error GoTo OpenQWSError

If loadFileName = "" Then
loadFileName = GetFileOfType("Excel", "xls")
End If

If loadFileName < "" Then
If oExcel Is Nothing Then
Set oExcel = CreateObject("Excel.Application")
Else
Set oExcel = GetObject(, "Excel.Application")
End If
Set oBook = oExcel.Workbooks.Open(loadFileName)
Set oSheet = oBook.Worksheets(sheetName)
oExcel.Visible = True
OpenQUOTEWorksheet = True
Else
MsgBox "No Quote Worksheet Selected."
OpenQUOTEWorksheet = False
End If

Exit Function

OpenQWSError:
MsgBox ("Error: " & Err.Number & "- " & Err.Description & ". Worksheet not
opened.")
Err.Clear
OpenQUOTEWorksheet = False
End Function

Now, I have to open the workbook this way. It is due to the way that the MRP
program is running. Therefore, I need the workbook to refresh the connection
after it is open. How would any of you go about making this happen?

I was thinking of adding a VB command into the ThisWorkBook portion to cause
it to go out when the sheet is opened.

Oh yeah, I also had a combo box that populated with a list when the book
opened. The population was written into a module. Opening this way caused
that to not work either. I fixed it by placing the code into the ThisWorkBook
section as a Private Sub Workbook_Open(). That is why I was thinking that
pointing a query at the table from here might be the solution.

So what is the best way to write the query into a Private Sub Workbook_Open()?

Thanks for any help,
LWhite

Sorry about the extra post but I forgot something. Is there a way to include
authorized access to the query so that any user can open that spreadsheet and
still see the data?

LWhite

Thisworkbook.RefreshAll

I don't understand your authorized access to the query question.

--
Regards,
Tom Ogilvy


"LWhite" wrote in message
...
Sorry about the extra post but I forgot something. Is there a way to
include
authorized access to the query so that any user can open that spreadsheet
and
still see the data?

LWhite

Sorry for the misleading question.

I have access to the database server but not all the users who will use my
spreadsheet do. If the spreadsheet is ran by a user who is not granted access
to the database then the refresh cannot occur. Is there a way to include the
login access in the spreadsheet without giving the various users access to my
databases as users listed in the MSSQL Enterprise Manager user list?

LWhite

"Tom Ogilvy" wrote:

Thisworkbook.RefreshAll

I don't understand your authorized access to the query question.

--
Regards,
Tom Ogilvy