Results 1 to 9 of 9

Thread: EXCEL and SQL

  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    EXCEL and SQL

    Is it possible to (in code) sql information on one worksheet and insert it into another worksheet in the same workbook?

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXCEL and SQL

    Theoretically, yes I think so, though you may run into problems because the SQL may be reading the version of your worksheet on disk rather than the version you have open (if it has changed).

    The question that sprang to mind, though, is why would you want to? What are you trying to do that you can't do using formulae or normal VBA?

    Jon

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXCEL and SQL

    The job I am trying to do is this: Bring text files into Access and then we need a portion of that data that is sql in order to make charts and then the end user will be able to access these charts on the web. All the data is going to change every two hours and the charts will all be dynamic. We are looking at several ways to do this....one is to bring all the data from access to excel and then query the excel spreadsheet to put the information into another spreadsheet that will also hold the chart. At the moment I am actually working a lot in ASP so I am not sure I am asking a question in the right place. I hope I haven't confused this too much. Thanks for any help or for at least answering and trying.

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXCEL and SQL

    Without wishing to stick my nose in too much, the questions that now come to mind are:
    1) Do you need to use Access? Can you bring the text files straight into Excel?
    2) If you do need to use Access (and hence have a query in Excel), why can't the charts refer directly to the queried data? Why do you need an intermediate step?
    3) Were you aware that you can export graphs (using a method of the chart object) to a gif file (and hence use in a web page)?

    Jon

  5. #5
    Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXCEL and SQL

    1. Access is not completely necessary, we already have that code that run at 2 hour intervals and they like that. Someone has tried to bring the text files directly into excel and they can't seem to add headers for the columns (the text files don't have field titles) which we have to have.
    2. We don't have to have the intermediate step. We have had problems in code running a query to access and then being able to refresh the data that it pulls in.
    3. Yes, we have successfully done that part.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXCEL and SQL

    What do you mean "your text files do not have headers"? You have to have field names in order to tell SQL which data you want to retrieve. If you want to use headers that are different than the field names, then create alias in your query.

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXCEL and SQL

    So, am I right in thinking that the problem has now become "How do we read text files straight into Excel with column headers?" so that you can use the data in charts. Then we can bypass queries and Access etc.

    Doesn't sound too hard, though it depends on the type and layout of the text files. What was the problem with this before?

    Jon

  8. #8
    Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXCEL and SQL

    The text files are just the data, no field names. For example the first five spaces may be a name and the next 25 may be an address.

  9. #9
    Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXCEL and SQL

    If I could do that, that would be great, but I still only need to be able query certain data out of the text file. This is why I was trying to bring it in and then query it. Am I missing something in how I should do this?

    The problem with bringing the text file in was that there never seemed to be anywhere that I could put the field name like you can do in access.

Posting Permissions

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