Click here to Skip to main content
15,742,973 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
My idea was let end user to open excel and create template for invoice. After that i would let him now that for column "Article Name" he need to put in the Name manager column name "NameArticle" and refers to that column and so on for other columns.

After that with loop i would read from my sql data and fill the data in Excel by those template.

I have one solution where this is currently working but it is terribly slow so i decided to quit it and look for another solution.

I tried to translate the code from my native language to be more understanding to you

What I have tried:

My solution was going smth like this.

Fill Class with Property Name and Property Value

Prepare rows
For j = 1 To bsItems.Count - 1
    For i = 1 To numberOfRowsPerItem
    Next i
Next j

After that i would copy the formated cells

For j = 1 To bsItems.Count - 1
    For i = 1 To numberOfRowsPerItem
        xlSheet.Rows(ItemStartRow + i).Copy
        xlSheet.Rows(ItemStartRow + (j * numberOfRowsPerItem) + i).PasteSpecial
    Next i
Next j

And fill values
For j = 1 To bsItems.Count
    For i = 1 To UsedParameters.Count
        cParametar = UsedParameters(i)

        MyPos.ExcelPos = xlBook.Names(cParametar).RefersTo
        DataRow= MyPos.Row+ (j * numberOfRowsPerItem)
        DataColumn= MyPos.Column

        If ParameterExists(cParametar) = True Then
            cValue= bsItems.Item(j).Item(cParametar).ValueOfItem
            cTekst = xlSheet.Cells(DataRow, DataColumn)
            cTekst = PrepareAndFormatCell(cTekst, cVrijednost)

            cNumFormat = xlSheet.Cells(DataRow, DataColumn).NumberFormat
            If InStr(1, cNumFormat, "0.00") <> 0 Then
                xlSheet.Cells(DataRow, DataColumn) = CDbl(cTekst)
                xlSheet.Cells(DataRow, DataColumn) = cTekst
            End If        
        End If
    Next i
Next j
Ralf Meier 1-Aug-17 2:07am    
Basicly, if you work with Excel, you do a kind of remote-controlling of Excel - so ... sorry ... that is a bit slow ...
But, what is your question now ?
Member 12859472 1-Aug-17 2:24am    
I want to create the Excel template for invoice where i will put my company header footer also i will name my columns as i want to. Your program need to be able to fill that into that Excel template
Ralf Meier 1-Aug-17 2:36am    
I understood - but when I see your code you are doing that allready ...
Member 12859472 1-Aug-17 2:37am    
Slow :(
Ralf Meier 1-Aug-17 3:09am    
It could be a bit faster if you create your template-sheet as *.xlsm (with VB-makro's in it) and give the data to the makro from your application and the makro fills the sheet ...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900