Beginner's problem

[Pohohanheim]

Hello.

I've created this kind of a basic code, which imports data from an access database to an excel sheet. I'am asking if anyone could help me with my code. My code is very primitive and if I have more than one customer in my database, if have to copy these lines to different modules for each customer. Naturally this makes code to become huge. Is there any way to create a "customer variable", which could "go" inside that query of mine, so I could save hundreds of lines of code? Thanks.



Sub info()
'


Dim customer As String
customer = Sheets("Customer").Range("a4")

Sheets("Data").Select
Range("J1299").Select
Selection.ClearContents
Range("A1").Select




If Customer = "STORE" Then

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DBQ=C:\info.mdb;DefaultDir=C:\;Driver=" _
), Array( _
"{Driver do Microsoft Access (*.mdb)};DriverId=25;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=" _
), Array("0;Threads=3;UserCommitSync=Yes;")), Destination:=Range("J4"))
.CommandText = Array( _
"SELECT Customers.ID, Customers.FirstName, Customers.LastName, Customers.Telno" & Chr(13) & "" & Chr(10) & "FROM `C:\info`.Customers Prof" _
, _
"iilit" & Chr(13) & "" & Chr(10) & "WHERE (Customers.ID='STORE')" & Chr(13) & "" & Chr(10) & "ORDER BY Customers.FirstName, Customers.LastName" _
)
.Name = "Query from Customers"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Range("A1").Select


Else

If customer= "STATION"
....
....
....
End If

End IF

End Sub

 

[HarleyQuinn]

I would modify your code as follows:

    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DBQ=C:\info.mdb;DefaultDir=C:\;Driver=" _
        ), Array( _
        "{Driver do Microsoft Access (*.mdb)};DriverId=25;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=" _
        ), Array("0;Threads=3;UserCommitSync=Yes;")), Destination:=Range("J4"))
        .CommandText = Array( _
        "SELECT Customers.ID, Customers.FirstName, Customers.LastName, Customers.Telno" & Chr(13) & "" & Chr(10) & "FROM `C:\info`.Customers Prof" _
        , _
        "iilit" & Chr(13) & "" & Chr(10) & "WHERE (Customers.ID='[red]" & Customer & "[/red]')" & Chr(13) & "" & Chr(10) & "ORDER BY Customers.FirstName, Customers.LastName" _
        )
        .Name = "Query from Customers"
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
    Range("A1").Select

You can then take out the IF statements and it will use the value of the Customer variable as the parameter for the query.

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[Pohohanheim]

Thanks a lot! Now it works like a dream!