automating query in VBA

daveyc18

Active Member
Joined
Feb 11, 2013
Messages
440
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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

daveyc18

Active Member
Joined
Feb 11, 2013
Messages
440
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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,933
Messages
5,471,607
Members
406,771
Latest member
Ch_powerdata

This Week's Hot Topics

Top