Solved

Export database data into Excel file

Posted on 2003-11-25
4
975 Views
Last Modified: 2012-06-21
I want to be able to export some data (coming from an sql statement) into an excel file. I am going to create a Form that will accept an input value and the select statement will query the database and will retrieve a number of records. What I want isto put each field into a column in the excel file. Is this possible?
0
Comment
Question by:tsiou
  • 2
4 Comments
 
LVL 1

Expert Comment

by:ms_praveen
ID: 9817253
Hi tsiou,

This is very easy and possible. You need to use VBA to address this. You can write code in the macro editor and referencing Microsoft Excel 9.0 Type library will be able to populate the columns.

Please let me know if you require the sample code.

ms_praveen
0
 
LVL 1

Expert Comment

by:ms_praveen
ID: 9817279
Hi tsiou,
Please try to paste this in the macro editor and try this is a sample
I am assuming you have a stored procedure to return the data

"Execute pubs.dbo.storedprocedure"

Please change the details like DSN and username password

I am picking the start date from C5 and end date from G5.

Hope this helps.

Please add anymore queries you have.

Private Sub AgentStats_Click()
On Error Resume Next

Dim drow As Integer
drow = 5

Application.StatusBar = "Preparing data on EXCEL REPORTS..."
Dim main_sheet As String, d_sheet As String
main_sheet = "AgentStats"

Dim stdt As String, enddt As String
Dim stdt1 As Date, enddt1 As Date

stdt1 = Format(Sheets(main_sheet).Cells(CStr(drow), "C").Value, "dd-mmm-yy") ', "dd/mm/yyyy") 'Sheets(main_sheet).Range("B3").Value
If Err.Number > 0 Then
    MsgBox "Invalid Start Date. Required format (dd-mmm-yy)", vbOKOnly, "Training"
    Cancel = True
    Err.Clear
    Exit Sub
End If
stdt = Sheets(main_sheet).Cells(CStr(drow), "C").Value


enddt1 = DateAdd("d", 4, stdt1)

Sheets(main_sheet).Cells(CStr(drow), "G") = enddt1  'Format(enddt1, "dd/mm/yyyy")
Sheets(main_sheet).Cells(CStr(1), "O") = "Issued on : " & Format(Now, "dd/MM/yyyy hh:nn:ss")

Sheets(main_sheet).Cells(CStr(7), "C") = DateAdd("d", 0, stdt1)
Sheets(main_sheet).Cells(CStr(7), "F") = DateAdd("d", 1, stdt1)
Sheets(main_sheet).Cells(CStr(7), "I") = DateAdd("d", 2, stdt1)
Sheets(main_sheet).Cells(CStr(7), "L") = DateAdd("d", 3, stdt1)
Sheets(main_sheet).Cells(CStr(7), "O") = DateAdd("d", 4, stdt1)


Dim conn As Connection
Set conn = New Connection
Dim rs As Recordset
Dim qs As String
Dim wbk As Workbook, wsht As Worksheet, shts As Sheets
Dim qdates() As String
Dim dcount() As String
Dim ix As Integer, iy As Integer, iz As Integer
Dim sx As String, cshtname As String
conn.Open "DSNNAME", "USER", "PWD"
conn.CommandTimeout = 0
Set wbk = Excel.ActiveWorkbook
Set shts = Worksheets

Dim sMR As String, MaxRows As Integer
Dim rowNum As Integer, colNum As Integer, rowNumd As Integer
rowNum = 9

stdt = Format(Sheets(main_sheet).Cells(CStr(drow), "C").Value, "dd-mmm-yy")
enddt = Format(Sheets(main_sheet).Cells(CStr(drow), "G").Value, "dd-mmm-yy")

sMR = Sheets(main_sheet).Cells(CStr(1), "AA").Value
If Len(sMR) > 0 Then
    MaxRows = Val(sMR) + 20
Else
    MaxRows = 100
End If
Sheets(main_sheet).Range("A" + Trim(Str(rowNum)) + ":Z" + CStr(MaxRows)).Clear

qs = "Execute pubs.dbo.storedprocedure'" + stdt + "'"

Set rs = conn.Execute(qs)
If Err.Number > 0 Then
MsgBox Err.Description
End If

If Not rs Is Nothing Then
    Do While Not (rs.EOF Or rs.BOF)

            Sheets(main_sheet).Cells(CStr(rowNum), "B") = rs.Fields("Agent")
           
            Sheets(main_sheet).Cells(CStr(rowNum), "C") = rs.Fields("C1")
            Sheets(main_sheet).Cells(CStr(rowNum), "E") = Replace(Replace("=IF(CRn>0,(DRn/CRn)*100,0)", "Rn", CStr(rowNum)), " ", "")
           
            Sheets(main_sheet).Cells(CStr(rowNum), "F") = rs.Fields("C2")
            Sheets(main_sheet).Cells(CStr(rowNum), "H") = Replace(Replace("=IF(FRn>0,(GRn/FRn)*100,0)", "Rn", CStr(rowNum)), " ", "")
           
            Sheets(main_sheet).Cells(CStr(rowNum), "I") = rs.Fields("C3")
            Sheets(main_sheet).Cells(CStr(rowNum), "K") = Replace(Replace("=IF(IRn>0,(JRn/IRn)*100,0)", "Rn", CStr(rowNum)), " ", "")
           
            Sheets(main_sheet).Cells(CStr(rowNum), "L") = rs.Fields("C4")
            Sheets(main_sheet).Cells(CStr(rowNum), "N") = Replace(Replace("=IF(LRn>0,(MRn/LRn)*100,0)", "Rn", CStr(rowNum)), " ", "")

            Sheets(main_sheet).Cells(CStr(rowNum), "O") = rs.Fields("C5")
            Sheets(main_sheet).Cells(CStr(rowNum), "Q") = Replace(Replace("=IF(ORn>0,(PRn/ORn)*100,0)", "Rn", CStr(rowNum)), " ", "")
           
            Sheets(main_sheet).Cells(CStr(rowNum), "R") = Replace(Replace("=CRn+FRn+IRn+LRn+ORn", "Rn", CStr(rowNum)), " ", "")
            Sheets(main_sheet).Cells(CStr(rowNum), "S") = Replace(Replace("=DRn+GRn+JRn+MRn+PRn", "Rn", CStr(rowNum)), " ", "")
            Sheets(main_sheet).Cells(CStr(rowNum), "T") = Replace(Replace("=IF(SRn>0,(RRn/SRn)*100,0)", "Rn", CStr(rowNum)), " ", "")
           
            rowNum = rowNum + 1
        rs.MoveNext
    Loop
End If

Sheets(main_sheet).Range("B" + CStr(rowNum + 1) + ":T" + CStr(rowNum + 1)).Borders(xlTop).Weight = 4
Sheets(main_sheet).Range("B" + CStr(rowNum + 1) + ":T" + CStr(rowNum + 1)).Borders(xlTop).ColorIndex = 49

Set rs = Nothing
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sheets(main_sheet).Range("B9:B" + CStr(rowNum)).Interior.ColorIndex = 24

Sheets(main_sheet).Range("C9:C" + CStr(rowNum)).Interior.ColorIndex = 15
Sheets(main_sheet).Range("D9:D" + CStr(rowNum)).Interior.ColorIndex = 48
Sheets(main_sheet).Range("E9:E" + CStr(rowNum)).Interior.ColorIndex = 16

Sheets(main_sheet).Range("F9:F" + CStr(rowNum)).Interior.ColorIndex = 15
Sheets(main_sheet).Range("G9:G" + CStr(rowNum)).Interior.ColorIndex = 48
Sheets(main_sheet).Range("H9:H" + CStr(rowNum)).Interior.ColorIndex = 16

Sheets(main_sheet).Range("I9:I" + CStr(rowNum)).Interior.ColorIndex = 15
Sheets(main_sheet).Range("J9:J" + CStr(rowNum)).Interior.ColorIndex = 48
Sheets(main_sheet).Range("K9:K" + CStr(rowNum)).Interior.ColorIndex = 16

Sheets(main_sheet).Range("L9:L" + CStr(rowNum)).Interior.ColorIndex = 15
Sheets(main_sheet).Range("M9:M" + CStr(rowNum)).Interior.ColorIndex = 48
Sheets(main_sheet).Range("N9:N" + CStr(rowNum)).Interior.ColorIndex = 16

Sheets(main_sheet).Range("O9:O" + CStr(rowNum)).Interior.ColorIndex = 15
Sheets(main_sheet).Range("P9:P" + CStr(rowNum)).Interior.ColorIndex = 48
Sheets(main_sheet).Range("Q9:Q" + CStr(rowNum)).Interior.ColorIndex = 16

Sheets(main_sheet).Range("R9:R" + CStr(rowNum)).Interior.ColorIndex = 15
Sheets(main_sheet).Range("S9:S" + CStr(rowNum)).Interior.ColorIndex = 48
Sheets(main_sheet).Range("T9:T" + CStr(rowNum)).Interior.ColorIndex = 16

Sheets(main_sheet).Range("B9:T" + CStr(rowNum)).BorderAround (xlHairline)
Sheets(main_sheet).Range("B9:T" + CStr(rowNum)).BorderAround (xlHairline)
Sheets(main_sheet).Range("B9:T" + CStr(rowNum)).Borders(xlInsideHorizontal).LineStyle = 1
Sheets(main_sheet).Range("B9:T" + CStr(rowNum)).Borders(xlInsideVertical).LineStyle = 1
Sheets(main_sheet).Range("B9:T" + CStr(rowNum)).Borders(xlInsideHorizontal).Weight = 2
Sheets(main_sheet).Range("B9:T" + CStr(rowNum)).Borders(xlInsideVertical).Weight = 2


Sheets(main_sheet).Range("E9:E" + CStr(rowNum)).NumberFormat = "#0"
Sheets(main_sheet).Range("H9:H" + CStr(rowNum)).NumberFormat = "#0"
Sheets(main_sheet).Range("K9:K" + CStr(rowNum)).NumberFormat = "#0"
Sheets(main_sheet).Range("N9:N" + CStr(rowNum)).NumberFormat = "#0"
Sheets(main_sheet).Range("Q9:Q" + CStr(rowNum)).NumberFormat = "#0"
Sheets(main_sheet).Range("T9:T" + CStr(rowNum)).NumberFormat = "#0"

Sheets(main_sheet).Cells(CStr(1), "AA") = rowNum

'Sheets(main_sheet).Range("C9:E" + CStr(rowNum)).HorizontalAlignment = 3


conn.Close
Set rs = Nothing
Set conn = Nothing
Application.StatusBar = "Ready"

End Sub


0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 9817285
also, you can do EXACTLY what you describe ENTIRELY within Excel, using the Get External Data tools

click on the Data button on the Menu, then choose Import External Data and follow the wizard.

AW
0
 

Accepted Solution

by:
ashishkumargupta earned 250 total points
ID: 9822727
HI TSIOU
 yes it would be possible coz i had done it
what u have to do for this
on form u have to create a button and write a trigger when button pressed.it will create a file .csv in your c root directory.you have to give the name for your file in on text field from where the trigger can fetch the file name like text.csv
for eg:-
declare

 al_id alert;
      al_button number;
       init      varchar2(4);
      C_CODE   varchar2(6);
  frm_date  varchar2(10);
  lessthan30 number;
   lessthan60 number;
   lessthan90  number;
   lessthan120  number;
   lessthan180  number;
   lessthan365 number;
  above365 number;
cursor c1 is
 select substr(a.vc_voucher_no,3)||','||c.vc_account_name||','||a.dt_voucher_date||','||sum(nvl(a.NU_TOT_ADVANCE,0) - nvl(a.NU_ADJUST_AMOUNT,0)) xx,
 a.vc_comp_code ,c.nu_account_code
       from       dt_tran_reference a, dt_transaction b,mst_account c
       where       
                --a.vc_comp_code = :comp_code and      
                a.vc_comp_code = b.vc_comp_code
               and           b.vc_comp_code=c.vc_comp_code
      and      b.nu_account_code =c.nu_account_code
                and         c.nu_account_code like c_code
                and          b.nu_serial_no=a.nu_serial_no
                and          b.vc_field1 is null
                --and b.rowid<=:rowid1
      and      a.vc_voucher_no = b.vc_voucher_no
      and      a.dt_voucher_date =b.dt_voucher_date
                --and          b.dt_voucher_date between '01-apr-01' and '31-mar-02'
                and         c.vc_acc_group_code in('1002001')
                and          b.dt_voucher_date<= frm_date
                and b.ch_tran_type not in ('SI')
having      sum(nvl(a.NU_TOT_ADVANCE,0) - nvl(a.NU_ADJUST_AMOUNT,0))<>0
group by c.vc_account_name,substr(a.vc_voucher_no,3),a.dt_voucher_date,a.vc_comp_code,c.nu_account_code
order by c.vc_account_name;
in_file    TEXT_IO.FILE_TYPE;
begin
set_application_property(cursor_style,'BUSY');
al_id := find_alert('alt_todate');
if :blk_main.Data_select='S' then
      C_CODE := to_char(:cust_code);
      Else
      C_CODE :='%' ;
      End if;
 frm_date:= to_char(:t_date,'dd-mon-yy');
       set_item_property('path',visible,property_true);
if :filename is null then
set_alert_property(al_id,alert_message_text,'File Name not specified !');
            al_button := show_alert(al_id);
            go_item(':blk_main.path');
             set_item_property(':blk_main.filename',insert_allowed,property_true);
             set_item_property(':blk_main.filename',update_allowed,property_true);
            RAISE Form_Trigger_failure ;
end if;
in_file :=TEXT_IO.FOPEN('C:\'||:filename||'.CSV', 'w');
TEXT_IO.PUT_LINE(IN_file, 'Voucher No'||','||'Account Name.'||','||'Voucher date'||','||'Unadjusted Amount');

for i in c1
loop

begin      
      select vc_comp_initial||'/' into init from mst_company
      where vc_comp_code=i.vc_comp_code;
   exception
      when no_data_found then
      init:=null;    
end;


TEXT_IO.PUT_LINE(IN_file, init||i.xx);
end loop;
TEXT_IO.FCLOSE(IN_FILE);
HOST('CMD/C C:\'||:filename||'.CSV',NO_SCREEN);
--end if;
set_application_property(cursor_style,'DEFAULT');
set_alert_property(al_id,alert_message_text,'File Created successfully !');
al_button := show_alert(al_id);

end;
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now