System Sign on during Macro Execution

Pls create a DSN for the database link. And use the DSN at the macro. U also check the ODBC connection string . We can save the password into it.

Try using:
.connection = "ODBC;DSN=myDatabase;Description=myDescription;UID=myID;PWD=myPassword
etc, just substitute the relevant details for each of the myWhatevers.
Regards

I can not do any thing with the ODBC connection only the Macro. Is that were is need to add .connection = "ODBC;DSN=myDatabase;Description=myDescription;UID=myID;PWD=myPassword
?

Yes e.g.
 

Sub refreshData()
    Range("A1").Select
    With Selection.QueryTable
        .Connection = _
        "ODBC;DSN=Finance SQL;Description=Finance SQL;UID=myID;PWD=myPassword;APP=Microsoft Office 2003;WSID=myComputer;DATABASE=myDB"
        .CommandText = Array("SELECT * FROM myDB.dbo.myTable myTable)
        .Refresh BackgroundQuery:=False
    End With
End Sub

Select allOpen in new window

Sorry, I don't understand. I do not need the marco to execute the refresh, but just add the user id and password when the log on screen is displayed.

Are you able to post your macro, maybe this will help us understand better.

Private Sub Workbook_Open()
  Dim DestFile As String
  Dim FileNum As Integer
  Dim ColumnCount As Integer
  Dim RowCount As Integer
  Dim NonBlank As Integer
  ' Select Range
  Range("B2").Select
  Selection.QueryTable.Refresh BackgroundQuery:=False
  Range("B2").Select
  Range(Selection, Selection.End(xlDown)).Select
  Range(Selection, Selection.End(xlToRight)).Select
  ' Obtain next free file handle number.
  FileNum = FreeFile()
  ' Turn error checking off.
  On Error Resume Next
  ' Attempt to open destination file for output.
  Open "RM Lot Registrations" For Output As #FileNum
  ' If an error occurs report it and end.
  If Err <> 0 Then
  MsgBox "Cannot open filename " & DestFile
  End
  End If ' Turn error checking on.
  On Error GoTo 0
  ' Loop for each row in selection.
  For RowCount = 1 To Selection.Rows.Count
  ' Loop for each column in selection.
  For ColumnCount = 1 To Selection.Columns.Count
  ' Determine cells length
  CellLength = Len(Selection.Cells(RowCount, ColumnCount).Text)
  ' Loop through each character to determine if there is a non-blank value
  For SearchString = 1 To CellLength
  CharValue = Mid(Selection.Cells(RowCount, ColumnCount).Text, SearchString, 1)
  If CharValue <> "    " Then
  ' Set flag to indicate a non-blank character was found
  NonBlank = 1
  End If
  Next SearchString
  If NonBlank = 0 Then
  Print #FileNum, """" & ""; " & """";"
  Else
  ' Write current cell's text to file with quotation marks.
  Print #FileNum, """" & Selection.Cells(RowCount, _
  ColumnCount).Text & """";
  ' Check if cell is in last column.
  End If
  NonBlank = 0
  If ColumnCount = Selection.Columns.Count Then
  ' If so, then write a blank line.
  Print #FileNum,
  Else
  ' Otherwise, write a comma.
  Print #FileNum, ",";
  End If
  ' Start next iteration of ColumnCount loop.
  Next ColumnCount
  ' Start next iteration of RowCount loop.
  Next RowCount
  ' Close destination file.
  Close #FileNum
End Sub

Try changing this:
 

Selection.QueryTable.Refresh BackgroundQuery:=False

Select allOpen in new window

to this:
 

With Selection.QueryTable
.Refresh BackgroundQuery:=False
.SavePassword = True
End With

Select allOpen in new window

This will save the ID and password for any future refreshes after the first.

Thanks but that did not work. The logon screen is displayed and is waiting for a password entry and OK to be selected. The ERP system that I am prompted to log on to does not have a save password option.

There have two way to solve the issue:
1) After open the xls file, locate the cell which contain the query table, then pls switch to excel menu [data],  [Refresh All],  [connection Properties](Excel 2010).
    At the Connection Properties dialog, switch the second page, you'll find the "Save password" check box, check it and edit the connection string box, input the password after "password=".
    Please check this dialog carefully if other options need be save.

2) You can add below codes before the line ("Selection.QueryTable.Refresh BackgroundQuery:=False"):
    Selection.QueryTable.Connection = "ODBC;DSN=your_DSN_Name;Description=myDescription;UID=myID;PWD=myPassword"
    Attention:
         The connection string is diffrence for diffrence ODBC driver, e.g. for the Access connction, it maybe like below:
    Selection.QueryTable.Connection = "ODBC;Provider=Microsoft.ACE.OLEDB.12.0;Password=1111;User ID=Admin;Data Source=C:\demo.mdb;Mode=Share Deny Write;Jet OLEDB:System database=;Jet OLEDB:Jet OLEDB:Database Password=;Jet OLEDB:Engine Type=4;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False"

    and for SQL server, the connection maybe like:
    QueryTable.Connection = "ODBC;Provider=SQLOLEDB.1;Password=1111.;Persist Security Info=True;User ID=sa;Initial Catalog=testdb;Data Source=sql_server;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=your_pc_name;Use Encryption for Data=False;Tag with column collation when possible=False"

Option 2 here is the same as was originally suggested in this thread, and is worth a try if you haven't done so already.

There is, of course, the option of editing your registry settings, although somewhat drastic it will handle the issue.

Thanks but I am using Excel 2003. Also what values should is used for DSN and Description? Not sure where to look for these.

You can either record a macro to do the full refresh and it will include this information or you could check through the ODBC settings:
Click on Start>Run>odbccp32.cpl and press Enter
Select the System DSN tab, choose the relevant data source and click on Configure. This will provide the Name, Description etc.
Do not make changes to that setup.