Automated credentials for ODBC query

[rywags11]

Hello

I have an Excel 2010 file with a macro built into it to refresh several tables drawing from an outside source (AS400/PRMS specifically).

The file is set up in a such a way that the tables are condensed & combined into an easily readable report. The report will be used by several different workgroups to manage their groups specific inventory levels.

When the Refresh macro is ran & all of the tables refresh in the background, the user is prompted to enter their credentials each time. Unfortunately, the workgroups do not require frequent use of AS400/PRMS, so dont manage thier credentials and we dont have any control over this.

To solve, I had our IT department set up generic credentials (user: MRLIT and password: MRLIT).

I would like to hard code the macro (in VBA, I suppose) to automatically enter these generic credentials without prompting the user to enter anything.

I'm not familiar with how to do this at all, so can someone please tell me how I could modify my code to make this happen? The refresh & report generation coding I'm using is below:

Sub RefreshDetail()
'
' RefreshDetail Macro
'
'
Application.ScreenUpdating = False
Sheets("Cust Sales History Data").Select
Range("A3").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Inventory data").Select
Range("A11").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Sheets("Inventory Data Detail").Select
Range("A3").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable2").PivotSelect "'Sum of 00004'", _
xlDataAndLabel, True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
' ActiveSheet.PivotTables("PivotTable2").PivotSelect "'Sum of 52 wk tot'", _
' xlDataAndLabel, True
' Selection.Borders(xlDiagonalDown).LineStyle = xlNone
' Selection.Borders(xlDiagonalUp).LineStyle = xlNone
' With Selection.Borders(xlEdgeLeft)
' .LineStyle = xlContinuous
' .ColorIndex = 0
' .TintAndShade = 0
' .Weight = xlThin
' End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Cells.Select
Selection.RowHeight = 16.5
Rows("1:1").Select
Selection.EntireRow.Hidden = True
Rows("3:3").Select
Selection.EntireRow.Hidden = True
Rows("2:2").RowHeight = 33.75
Columns("B:B").EntireColumn.AutoFit
Range("A1").Select
End Sub