1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Probably Impossible Macro

Discussion in 'Business Applications' started by randomwyguy, Jul 20, 2017.

Thread Status:
Not open for further replies.
  1. randomwyguy

    randomwyguy Thread Starter

    Joined:
    Jul 20, 2017
    Messages:
    1
    Assume I know nothing (yes my name is Jon).

    Anyway, I want excel to go to a url, populate some fields (date range, location), run the report, then download it in csv format.

    The url is actually an internal SQL database so this should be simple via just using a SQL table. However, my company won't give me backdoor access to the server to connect to the table.

    I can however, go to the url (not sure if website or url or what is the correct term) and manually input everything, run the report, export as .csv, copy, paste, and go from there. I have to do this everyday when the previous days information is uploaded to the database. There could also be changes made to other days which I need to update as well.

    Is there a way I can automate this without having to bother my IT group with server permissions?
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,580
    First Name:
    Hans
    Hi Jon, welcome to the forum.
    One way or another you do have access to the SQL database, so how doe you do that?
    You mention that you to run a report and export is as csv.
    Processing the csv is no problem, you could do it yourself by recording a macro in Excel that imports / opens the csv file and does what you need and save it as an Excel file.
    Give this a thought and see if you can work it out.
    You could ask the IT group to create a user that only has read writes, We did this where I worked so that you could establish a ODBC connection and access the data required in Excel or any other application.
    Our username was generally 'reporter'
     
  3. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    I build my hyper link like below and it takes me to the webpage with the criteria for my search already built in. I simply download the csv when I get there. Depending how your source is set up similar may work for you.
    Code:
    strEndDate = Date
    strStartDate = DateAdd("d", 1, DateValue(Me.txtEmpChgDate))
    strHyp1 = "http://foundry.YourNetwork.net/ReportServer/Pages/ReportViewer.aspx?/OST/Repository/EmployeeReporting/RepositoryEmpChgsByDateRange&rs:command=Render&BeginDate=xx/xx/xxxx&EndDate="
    strHyp2 = Replace(strHyp1, "xx/xx/xxxx", strStartDate)
    strHyp3 = strHyp2 & strEndDate
    'MsgBox strHyp3
    Application.FollowHyperlink strHyp3
    
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1193429

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice