Problem with the import of a database table into Excel 2003.

[Guest]

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

 

[Guest]

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

 

[Tom Ogilvy]

Thisworkbook.RefreshAll

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

 

[Guest]

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