Export database data into Excel file

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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

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

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.

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

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.