Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Export database data into Excel file

Posted on 2003-11-25
Medium Priority
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?
Question by:tsiou
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Expert Comment

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.


Expert Comment

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
    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
    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
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

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

End Sub

LVL 44

Expert Comment

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.


Accepted Solution

ashishkumargupta earned 1000 total points
ID: 9822727
 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 will create a file .csv in your c root 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:-

 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
                --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;
al_id := find_alert('alt_todate');
if :blk_main.Data_select='S' then
      C_CODE := to_char(:cust_code);
      C_CODE :='%' ;
      End if;
 frm_date:= to_char(:t_date,'dd-mon-yy');
if :filename is null then
set_alert_property(al_id,alert_message_text,'File Name not specified !');
            al_button := show_alert(al_id);
            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

      select vc_comp_initial||'/' into init from mst_company
      where vc_comp_code=i.vc_comp_code;
      when no_data_found then

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


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA:…

670 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