Community discussion forum

Exporting Recordset to excel file

• • Reply

  2 years ago

  by rupin1984

  rupin chheda

  , India

  Joined 3 years ago

  Hi All,

  Is it possible to export an entire recordset to an newly created excel file..

  I mean i know its possible but can it be done directly instead of the kind of brute force method of writing each an every element of a recordset...something like which works for any recordset..

  Thanks

  Rupin

  Report abuse
• • Reply

  2 years ago

  by addi82

  Aditya Singh

  Lucknow, India

  Joined 2 years ago

  Dear Rupin,

   

  If I understood your problem correctly , then the solution is-

   

  From references add the “MS excel object library”. Use the code-

   

   

  Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

   

  Public conn As New ADODB.Connection

  Dim rs As New ADODB.Recordset

  Dim xl As New Excel.Application

  Dim xlsheet As Excel.Worksheet

  Dim xlwbook As Excel.Workbook

   

   

   

  Private Sub Command1_Click()

     i = 1    

     While rs.EOF = False         

      xlsheet.Cells(i, 1) = rs.Fields("field1")

      xlsheet.Cells(i, 2) = rs.Fields("field2")

     i = i + 1

     Wend 

      xlwbook.Save

      xl.Quit

      openels = ShellExecute(hWnd, "Open", "c:\book1.xls", "", "c:\book1.xls", 1)

  End Sub

   

  Private Sub Form_Load()

      conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\test.MDB;Persist Security Info=False"

      conn.Open

          If rs.State = adStateOpen Then rs.Close

          rs.CursorLocation = adUseClient

          rs.Open "SELECT * FROM tablename", conn, adOpenKeyset, adLockOptimistic

      Set xlwbook = xl.Workbooks.Open("c:\book1.xls")

      Set xlsheet = xlwbook.Sheets.Item(1)

  End Sub

   

  Private Sub Form_Unload(Cancel As Integer)

      Set xlwbook = Nothing

      Set xl = Nothing

  End Sub

   

  Hope  this would work for you.

   

  Regards,

   

  Aditya Singh

  Report abuse
• • Reply

  2 years ago

  by rupin1984

  rupin chheda

  , India

  Joined 3 years ago

  Thanks a lot Aditya! It works..but i want to add the filed names as column names..how should i do that..also how is a cell in the xlsheet addressed...as(i,j) what do i and j stand for...i->rows

  j->columns or vice versa..Thanks for you help on older posts too

  Report abuse
• • Reply

  2 years ago

  by addi82

  Aditya Singh

  Lucknow, India

  Joined 2 years ago

  Hi Rupin,

  You may use field name (or any other name) as column name as

  Private Sub Command1_Click()

  xlsheet.Cells(1, 1) = “header1”

  xlsheet.Cells(1, 2) = “header2”

  xlsheet.Cells(1, 3) = “header3”

  Range("A1:A3").Select

  With Selection.Font
           .Name = "Arial"
           .FontStyle = "Bold"
           .Size = 12
  End With
  
  
  
  

  Selection.Columns.AutoFit

  i = 2   '// i stands for rows

  While rs.EOF = False         

  xlsheet.Cells(i, 1) = rs.Fields("field1")

  xlsheet.Cells(i, 2) = rs.Fields("field2")

  xlsheet.Cells(i, 3) = rs.Fields("field3")

  i = i + 1

  Wend 

  xlwbook.Save

  xl.Quit

  openels = ShellExecute(hWnd, "Open", "c:\book1.xls", "", "c:\book1.xls", 1)

  End Sub

  Hope this would help you more.

  Regards,

  Aditya Singh

  Report abuse
• • Reply

  2 years ago

  by addi82

  Aditya Singh

  Lucknow, India

  Joined 2 years ago

  Add one more line required in the code-

  i = i + 1

  rs.MoveNext 

  Wend 

   

  Regards,

  Aditya Singh

   

   

  Report abuse
• • Reply

  2 years ago

  by SivaMsc

  SivaMsc

  TamilNadu, India

  Joined 2 years ago
  i want to import data from Excel sheet, and aslo to import data from required sheet in excel ......
  
  By SivaKumar
  
  
  
  
  Report abuse
• • Reply

  2 years ago

  by SivaMsc

  SivaMsc

  TamilNadu, India

  Joined 2 years ago
  Hi,
  
   i want to generate automatic ID , show for example
  
    Cust001omer
    Cust002omer
    Cust003omer
    Cust004omer
  
  
  like this i want to generate automattic, am already finished by generating automatiic like Cust1, Cust2,Cust3
  
  
  
  
  
  
  
  
  
  
  
  
  Report abuse
• • Reply

  2 years ago

  by rupin1984

  rupin chheda

  , India

  Joined 3 years ago

  Hi,

  I am unable to use this code that you have provided.

  Dim xlsheet As Excel.Worksheet

  Dim xlwbook As Excel.Workbook

  
  Private Sub Command1_Click()
  openels = ShellExecute(hWnd, "Open", "c:\book1.xls", "", "c:\book1.xls", 1)
  
  

   Set xlwbook = xl.Workbooks.Open("c:\book1.xls")

  Set xlsheet = xlwbook.Sheets.Item(1)

   

  For i = 0 To 10
  For j = 0 To 10
  

  xlsheet.Cells(i, j) = i * j
  Next j
  Next i
  xlwbook.Savexl.Quit
  End Sub
  
  
  
  

   

   

   

   

  can you please improvise and let me know what am i doing wrong.the error it gives me is application error or runtime error..

   

  Report abuse
• • Reply

  2 years ago

  by addi82

  Aditya Singh

  Lucknow, India

  Joined 2 years ago

  Hi,

  In which lines you are getting error.

  Please specify, then I would try for solution.

  Regards,

  Aditya 

  Report abuse
• • Reply

  2 years ago

  by deltaalfa

  deltaalfa

  , Viet Nam

  Joined 2 years ago
  I want to hyperlink a field named "picture" in table "mytable" using ShellExecute.

  How do I specify the table name and field name in my path as a string? Do I have to necessarily establish a connection to the database?

  The current code, which obviously is not working, is as follows:

   Dim strDocName As StringstrDocName = "C:\NJCMS\GIS Interchange flymap for skycomp\Interchange Operational Analysis\flylocations_SpatialJoin.dbf" & Picture

  Call ShellExecute(0, "open", strDocName, vbNullString, vbNullString, 2)

  Report abuse
• • Reply

  2 years ago

  by deltaalfa

  deltaalfa

  , Viet Nam

  Joined 2 years ago

  Hi,
  I want to hyperlink a field named “picture” in table "mytable" using ShellExecute. The file extensions(.jpg) are already in the filenames.
  How do I specify the table name and field name in my path as a string?
  The current code, which obviously is not working, is as follows
  
  Dim strDocName As String
  'strDocName = "C:mytable.dbf" & me.picture
   Call ShellExecute(0, "open", strDocName, vbNullString, vbNullString, 2)
  

  Report abuse