Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

Macros, Excel and MSQuery data exchange !

Hi,

We?re ?desperately trying to perform a task within MS Excel & MS Query.

Transferring data from Query into a spreadsheet is no problem for us.

However we would love to be able to create a Marco which - copies the value of an input cell within Excel,
pastes it into the SQL statement (or a Criteria field) within Query, update the query and refresh the spreadsheet.

We have attempted the above using our database but have found that the Macro fails when Run.

Can anyone help us with this problem ? (None of us here are ?clued up? with VB ? so be gentle !)

We have thought about using DDE but don?t understand this either !!!!!!!!!

The VB routine is shown below (we have made BOLD the first error line) ;


Sub Macro2()
'
' Macro2 Macro
' Macro recorded 27/06/2001 by Neil Booth
'

'
    Range("A1").Select
    Selection.Copy
    With Selection.QueryTable
        .Connection = Array(Array( _
        "ODBC;Driver={SCO Vision ODBC};SERVER=bwt;UID=nab;PWD=katie;Hostname=bwt;ServerID=Informix;DBname=/u1/Strategix/db/tool/baxt;DBuser=;" _
        ), Array("DBauth=;DBoptions=;"))
        .CommandText = Array( _
        "SELECT ndmas.ndm_ndcode, ndmas.ndm_name" & Chr(13) & "" & Chr(10) & "FROM informix.ndmas ndmas" & Chr(13) & "" & Chr(10) & "WHERE (ndmas.ndm_ndcode='ADV001')" _
        )
        .Refresh BackgroundQuery:=False
    End With
End Sub



Yours hopefully,
0
NeilBooth
Asked:
NeilBooth
1 Solution
 
TimCotteeCommented:
Having created the query once, you don't need to add it again so the macro should read:


   With ActiveSheet.QueryTables(1)
.CommandText = Array( "SELECT ndmas.ndm_ndcode, ndmas.ndm_name" & Chr(13) & "" & Chr(10) & "FROM informix.ndmas ndmas" & Chr(13) & "" & Chr(10) & "WHERE (ndmas.ndm_ndcode='" & Range("A1").Value & "')" )
       .Refresh BackgroundQuery:=False
   End With
End Sub

This will use the value of the cell A1 to filter the returned data.
0
 
DanRollinsCommented:
Hi NeilBooth,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will suggest to:

    Accept TimCottee's comment(s) as an answer.

NeilBooth, if you think your question was not answered at all or if you need help, you can simply post a new comment here.  Community Support moderators will follow up.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
Computer101Commented:
Comment from expert accepted as answer

Computer101
E-E Moderator
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now