Using Excel to really power Navision

Shenpen

Hi all,

I recently have some developments that I am thinking it should be in Excel instead of Navision. They are kind of a reports, but they have many calculations.

A typical example is cash-flow. You could put your bank/cash accounts on one sheet, vendor balances on another sheet, customer balances on another sheet sheet, sales shipments and sales orders on another one and then you can sum add them together on another sheet and then you have a decent cash-flow plan.

Yes of course Jet Reports is a perfect tool for it, but

1) it costs money, and I it would hard to explain to my customer
2) it has some limitations, compared to the power of MS SQL stored procedures

So what I would like to do in this topic is to start to create a methodology on (MS SQL Server Option) Navision to Excel reporting / data extraction.
It is far from complete, actually it is the first time I write VBA code. So I would like some help...

Basic ideas:

- data should be provided by a SQL stored proc with parameters for "flowfiltering"
- it is called from Excel VBA
- nothing is hardcoded into Excel VBA, every parameter is from sheets, so we must write it only once and happily use it 1000 times

1) SQL stored proc

This is my first attempt. It is just for testing the idea - a simple G/L report with parameters for g/l acc, date from and date to

use report
go
CREATE  PROCEDURE dbo.QueryTest (
			@par1 varchar(20)= null, --gl account
			@par2 varchar(11) = null,   -- from date
			@par3 varchar(11) = null    -- to date
			)
	
AS
	select  
	gle.[G_L Account No_] GLAccNo,
	max(glacc.[name]) Name,
	sum(gle.amount) Amount
	
		

	from navi.dbo.[Cronus Rt_$G_L Account] glacc left join
	     navi.dbo.[Cronus Rt_$G_L Entry] gle (gle.[G_L Account No_]=glacc.[No_])
 
	     	
	where 
		((@par1 is null)or(gle."G_L Account No_"=@par1))
		and
		((@par2 is null)or (gle."posting date">=convert(datetime,@par2,102)) )
		and
		((@par3 is null)or(gle."posting date"<=convert(datetime,@par3,102)))
			
	group by gle."G_L Account No_"

Some explanation:
Navi is the navision database
report is a separate database
parameters named par1, par2 because if we write an Excel VBA that gives 10 parameters taken from B1 .. B10 cells then we need to standardize the parameter names
it seems we have to have only varchar parameters and convert them later
the isnulls in the where clause are needed to allow NOT entering any parameter at all
format 102 is the Hungarian date format (yyyy.mm.dd) of course you must use your country's date format

Problem: Company is hardcoded. Solution: we have to supply that also as a parameter. But it means we must write the query to a string variable and EXEC it. And it means we have to write stuff like 'select a,b,c' + @dbandcompname + 'Item]' and supply a parameter of '[Navi..Cronus Rt_$' .... yuck. Any better idea?...

2) for the Excel part:
On the first sheet:
- B1..B10 hold are parameters
- D1 holds for the stored proc to call (dbo.QueryTest)
- D2 holds connectstring (ODBC;DSN=NAVI;UID=sa;Database=report)
- D3 holds the name of the sheet to put the results (Sheet 2 or whatever)

And there I added a button which calls the following VERY BAD VBA code (yes, I know it's terrible, this is the first time I write VBA):

Sub RunQuery()
Dim qt As QueryTable
SqlString = "execute " & Range("D1").Value
If ActiveSheet.Range("B1").Value <> "" Then
   SqlString = SqlString & " " & "@par1=" & "'" & ActiveSheet.Range ("B1").Value & "'"
End If ' first parameter starts with space

If ActiveSheet.Range("B2").Value <> "" Then
   SqlString = SqlString & "," & "@par2=" & "'" & ActiveSheet.Range("B2").Value & "'"
End If 'other parameters start with a ,

If ActiveSheet.Range("B3").Value <> "" Then
   SqlString = SqlString & "," & "@par3=" & ActiveSheet.Range("B3").Value
End If

If ActiveSheet.Range("B4").Value <> "" Then
   SqlString = SqlString & "," & "@par4=" & ActiveSheet.Range("B4").Value
End If

If ActiveSheet.Range("B5").Value <> "" Then
   SqlString = SqlString & "," & "@par5=" & ActiveSheet.Range("B5").Value
End If

If ActiveSheet.Range("B6").Value <> "" Then
   SqlString = SqlString & "," & "@par6=" & ActiveSheet.Range("B6").Value
End If

If ActiveSheet.Range("B7").Value <> "" Then
   SqlString = SqlString & "," & "@par7=" & ActiveSheet.Range("B7").Value
End If

If ActiveSheet.Range("B8").Value <> "" Then
   SqlString = SqlString & "," & "@par8=" & ActiveSheet.Range("B8").Value
End If

If ActiveSheet.Range("B9").Value <> "" Then
   SqlString = SqlString & "," & "@par9=" & ActiveSheet.Range("B9").Value
End If

If ActiveSheet.Range("B10").Value <> "" Then
   SqlString = SqlString & "," & "@par10=" & ActiveSheet.Range("B10").Value
End If

MsgBox (SqlString) ' for debugging
ConnString = Range("D2").Value
QuerySheet = Range("D3").Value
Sheets(QuerySheet).Select
If ActiveSheet.QueryTables.Count = 0 Then
    With ActiveSheet.QueryTables.Add(Connection:=ConnString, Destination:=Range("A1"), Sql:=SqlString)
       .Refresh
    End With
Else
    ActiveSheet.QueryTables.Item(1).Sql = SqlString
    ActiveSheet.QueryTables.Item(1).Refresh
    
End If
End Sub

Yeah, I told you it' s bad:

- Most important problem is: if I supply parameters in f.e. B1 and B2 then it creates the exec dbo.QueryTest @par1='3456' , @par2='2005.05.05' SQL string which is right, but if I leave the first parameter empty then parameter list starts with a comma and that is bad

- this is certainly not the way to do it, I should write a for loop, but I don't really know the VBA syntax yet (you can help or I will find out for myself and post it here)

- it is important to set all parameter cells to text ...

- what I still have totally no ideaa about is that if I need to fill an exactly defined cell or range f.e. C12 or C10..C12 how do I do that?

I will post here if I find out important information.

Shenpen

I have corrected the VBA part, now it works and is much more elegant:

Sub RunQuery()
    Dim qt As QueryTable
    Dim ParamCell As Range
    Dim FirstParam As Boolean
    Dim ParamNo As Integer
    Dim SQLString As String
    Dim ConnString As String

    SQLString = "execute " & Range("D1").Value
    FirstParam = True
    ParamNo = 1
    For Each ParamCell In Range("B1:B10")
        If Not IsEmpty(ParamCell) Then
            If FirstParam = True Then
                SQLString = SQLString & " "
                FirstParam = False
            Else
                SQLString = SQLString & ","
            End If
            SQLString = SQLString & "@par" & ParamNo & "= '" & ParamCell.Value & "'"
        End If
        ParamNo = ParamNo + 1
    Next ParamCell

    MsgBox (SQLString)

    ConnString = Range("D2").Value
    QuerySheet = Range("D3").Value
    Sheets(QuerySheet).Select
    If ActiveSheet.QueryTables.Count = 0 Then
        With ActiveSheet.QueryTables.Add(Connection:=ConnString, Destination:=Range("A1"), Sql:=SQLString)
        .Refresh
        End With
    Else
        ActiveSheet.QueryTables.Item(1).Sql = SQLString
        ActiveSheet.QueryTables.Item(1).Refresh
    
    End If
End Sub

Problems:
- company name problem still not solved
- I don't really like thin Item(1).Refresh thing... any better idea?
- still don't know how to give a give a cell or range an SQL value