Results 1 to 3 of 3

Thread: automating query in VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2013
    Posts
    360
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default automating query in VBA

    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

  2. #2
    Board Regular
    Join Date
    Feb 2013
    Posts
    360
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: automating query in VBA

    Quote Originally Posted by daveyc18 View Post
    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

  3. #3
    Board Regular
    Join Date
    Feb 2013
    Posts
    360
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: automating query in VBA

    anybody?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •