automating query in VBA

[daveyc18]

not sure if this can be done, but every day I download data via an Excel add-in ("transfer data from iseries") which I beleive is a way to download data from AS400.

Every day, I need to click the Excel add-in, enter my user name and password (which is painful) then i tell the admin to start the download from cell A1; include column headings.

is there a way to macro this so I dont need to constantly enter my user name and password?

The file path I enter when I click the add in is..."c:\Downloads\bond margin.tto"... I enter this in the "Create from file" field.


the server name is "BLUE.TOR.mccloud.COM" if that helps

 

[daveyc18]

not sure if this can be done, but every day I download data via an Excel add-in ("transfer data from iseries") which I beleive is a way to download data from AS400.

Every day, I need to click the Excel add-in, enter my user name and password (which is painful) then i tell the admin to start the download from cell A1; include column headings.

is there a way to macro this so I dont need to constantly enter my user name and password?

The file path I enter when I click the add in is..."c:\Downloads\bond margin.tto"... I enter this in the "Create from file" field.


the server name is "BLUE.TOR.mccloud.COM" if that helps

found some code online,b ut it gives me an error "method open of object recordset failed"....please help

Set conn = CreateObject("adodb.connection")
Set rcd = CreateObject("adodb.recordset")
cnnstr = "DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=BLUE.TOR.mccloud.COM;USERID=dwight;PWD=123456"




conn.Open cnnstr


Set rcd.ActiveConnection = conn




sqlstm = "SELECT * FROM c:\Downloads\bond margin.tto"




rcd.Open sqlstm


Set flds = rcd.Fields
For i = 0 To flds.Count - 1
Cells(1, i + 1).Value = flds(i).Name
Next i


j = 2
Do While Not (rcd.EOF)


For i = 0 To flds.Count - 1
Cells(j, i + 1).Value = flds(i).Value
Next i


rcd.movenext
j = j + 1
Loop




Set rcd = Nothing
Set conn = Nothing

 

[daveyc18]

anybody?