automating query in VBA

daveyc18

Well-known Member
Joined
Feb 11, 2013
Messages
707
Office Version
  1. 365
  2. 2010
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top