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