Import Data Keeps asking for Password
I have a spreadsheet that imports data from an access database query. The database is password protected (I do have the password). I have gone to the data properties and checked the box that says "Save Password" However, when I refresh the data I get numerous prompts for the databases's password and location. Some of these pop-ups have a password already in them and some do not. I would like to have to where this prompt does not even display and the password is stored to get the new information. The data refreshes only when the form is open suing the following code in VBA. Any help would be greatly appreciated. Private Sub Workbook_Open() On Error Resume Next 'Procede to next skip on alerts Dim Test1 As Boolean 'To test If Statement 'Start at Sheet 1 or Complaint Date Worksheets("Complaint Data").Select Worksheets("Complaint Data").Range("A1").Select 'The remaiing code will only run as long as there is no data already in "A2" 'This prevents Excel from trying to update once it is already updated If Worksheets("Complaint Data").Range("A2") = "" Then Dim CDStr As Range Dim CDStp As Range Dim FCDRng As Range Dim GCDRng As Range Application.DisplayAlerts = False 'Turn off all alerts while data is updated Application.ScreenUpdating = False 'Use to speed up process and remove screen flicker Workbooks(1).RefreshAll 'refresh data, pivot tables and charts Application.DisplayAlerts = True 'turn alets back on 'Range of first row of data to last row of data Set CDStr = Worksheets("Complaint Data").Range("A2") Set CDStp = Worksheets("Complaint Data").Range("A2").End(xlDown) 'Set up ranges to manipulate data Set FCDRng = Worksheets("Complaint Data").Range(CDStr.Offset(0, 5), CDStp.Offset(0, 5)) Set GCDRng = Worksheets("Complaint Data").Range(CDStr.Offset(0, 6), CDStp.Offset(0, 6)) 'Turn data from database that is labeled as True or False into a Yes or No For Each c In FCDRng If c.Value = "False" Then c.Value = "No" ElseIf c.Value = "True" Then c.Value = "Yes" End If Next For Each c In GCDRng If c.Value = "False" Then c.Value = "No" ElseIf c.Value = "True" Then c.Value = "Yes" End If Next 'Change focus on Pivot Chart sheet to empty cell Worksheets("Pivot Tables").Select Worksheets("Pivot Tables").Range("D1").Select Worksheets("Pivot Tables").Range("I1").PivotTable.RefreshTable ' To update true false on table to Yes no Worksheets("Pivot Tables").Range("I9").PivotTable.RefreshTable ' To update true false on table to Yes no 'Return screen updating to show new data Application.ScreenUpdating = True 'Return focus to first sheet Worksheets("Complaint Data").Select Worksheets("Complaint Data").Range("A2").Select Else Test1 = False 'only used for testing purposes End If End Sub -- Dominator ------------------------------------------------------------------------ Dominator's Profile: http://www.excelforum.com/member.php...o&userid=18504 View this thread: http://www.excelforum.com/showthread...hreadid=376603 |
All times are GMT +1. The time now is 01:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com