Export database data into Excel file

Posted on 2003-11-25
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 250 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

 Watch the Recording: Learning MySQL 5.7

MySQL 5.7 has a lot of new features. If you've dabbled with an older version of MySQL, it is definitely worth learning.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

626 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