Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

System Sign on during Macro Execution

Posted on 2011-05-03
14
Medium Priority
?
305 Views
Last Modified: 2012-05-11
I have a macro that refreshes data from an ODBC linked database into an excel spreadsheet. However during the refresh the linked system logon detail (used id & password) are requested. How can I code the marco to completed these details so that I do not have to enter them each time?
0
Comment
Question by:MarkRichardStanley
  • 6
  • 5
  • 3
14 Comments
 
LVL 7

Expert Comment

by:huacat
ID: 35514743
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.
0
 
LVL 7

Expert Comment

by:philip m o'brien
ID: 35514774
Try using:
.connection = "ODBC;DSN=myDatabase;Description=myDescription;UID=myID;PWD=myPassword
etc, just substitute the relevant details for each of the myWhatevers.
Regards
0
 

Author Comment

by:MarkRichardStanley
ID: 35515051
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
?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 7

Expert Comment

by:philip m o'brien
ID: 35515228
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

Open in new window

0
 

Author Comment

by:MarkRichardStanley
ID: 35515453
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.
0
 
LVL 7

Expert Comment

by:philip m o'brien
ID: 35515498
Are you able to post your macro, maybe this will help us understand better.
0
 

Author Comment

by:MarkRichardStanley
ID: 35677129
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

0
 
LVL 7

Expert Comment

by:philip m o'brien
ID: 35688132
Try changing this:
 
Selection.QueryTable.Refresh BackgroundQuery:=False

Open in new window

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

Open in new window

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

Author Comment

by:MarkRichardStanley
ID: 35688233
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.
0
 
LVL 7

Expert Comment

by:huacat
ID: 35688378
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"
0
 
LVL 7

Expert Comment

by:philip m o'brien
ID: 35688459
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.
0
 

Author Comment

by:MarkRichardStanley
ID: 35688564
Thanks but I am using Excel 2003. Also what values should is used for DSN and Description? Not sure where to look for these.
0
 
LVL 7

Expert Comment

by:philip m o'brien
ID: 35688615
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.
0
 
LVL 7

Accepted Solution

by:
huacat earned 1000 total points
ID: 35695177
For Excel 2003, pls use main menu item, [data], [import external data], [edit query], on the [Edit OLE DB query] dialog, the top box should show the connection string.
if the string not contain the "user id" & "password" partition, pls add it like below:
User ID=your_user_name;Password=your_password;
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question