Exporting data to Excel 1

[shaminda]

I'm writing a phone listing application in VB using an Access database. I want to list it in an Excel file. and the out put should look like<br><br>Name Phone&nbsp;&nbsp;Name&nbsp;&nbsp;Phone<br>A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;G&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7<br>B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;H&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;8<br>C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;I&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;9<br>D&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;J&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10<br>E&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;K&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;11<br>F&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;L&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;12<br><br>I'm using DAO, and want it like this so all the data will fit on one page.

 

[efrain]

Why would you want to use excel?&nbsp;&nbsp;Seems to me that Access is where you would want to stick to.&nbsp;&nbsp;Try using a query or crystal reports.

 

[rekclaw]

This should not be too much of a problem unless you are generating a huge list.&nbsp;&nbsp;If you extracting more than 1 or 2 hundred rows, I would consider opening Access as an OLE object and have it generating the spreadsheet for you.&nbsp;&nbsp;<br><br>I've copied the following code from one of my existing projects.&nbsp;&nbsp;The project uses RDO to extract data from an Oracle data base.&nbsp;&nbsp;I've attempted to make the change to DAO on the fly, but it my not work on the first try.&nbsp;&nbsp;You may have to tweak it a little more.&nbsp;&nbsp;Remember, this code isn't tested so no guarantees.&nbsp;&nbsp;Hope it helps.<br><br>&nbsp;&nbsp;&nbsp;&nbsp;on error resume next<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim objExc As Excel.Application<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim n As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim wrkJet As Workspace<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim db As Database<br>&nbsp;&nbsp;&nbsp;&nbsp;dim rs as Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;dim sSql as string<br>&nbsp;&nbsp;&nbsp;&nbsp;dim i as integer <br>&nbsp;&nbsp;&nbsp;&nbsp;dim iCount as integer <br>&nbsp;&nbsp;&nbsp;&nbsp;Set wrkJet = CreateWorkspace(&quot;&quot;, &quot;admin&quot;, &quot;&quot;, dbUseODBC)<br>'&nbsp;&nbsp;&nbsp;You will need to set the ODBC-DSN in the following line<br>&nbsp;&nbsp;&nbsp;&nbsp;Set db = wrkJet.OpenDatabase(ODBC-DSN, dbDriverComplete, False, &quot;ODBC;&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;Screen.MousePointer = vbHourglass<br>'&nbsp;&nbsp;&nbsp;Insert your select statement in sSql<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)<br>&nbsp;&nbsp;&nbsp;&nbsp;i = 0<br>&nbsp;&nbsp;&nbsp;&nbsp;iCount = 0<br>&nbsp;&nbsp;&nbsp;&nbsp;if err = 0 then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if rs.eof = false then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Set objExc = New Excel.Application<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;objExc.Workbooks.Add<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;objExc.Worksheets.Add<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;objExc.Visible = True<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end if<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;do while (rs.eof = False)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if mod(iCount, 7) = 0 then<br>'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Moving the focus of the cells over two columns<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;objExc.range(&quot;A1&quot.select<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;objExc.ActiveCell.Offset(0, i).Select<br>'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Build your column headings<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;for n = 0 to rs.fields.count - 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;objExc.ActiveCell.Offset(0, n).Value = rs.fields.name<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Next n<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;i = i + rs.fields.count<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end if<br>'&nbsp;&nbsp;&nbsp;Advance to the next row<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;objExc.ActiveCell.Offset(1, 0).Select<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;for n = 0 to rs.fields.count - 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;objExc.ActiveCell.Offset(0, n).Value = rs.fields.value<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Next n<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;iCount = iCount + 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rs.movenext<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;loop<br>&nbsp;&nbsp;&nbsp;&nbsp;end if<br>&nbsp;&nbsp;&nbsp;&nbsp;Screen.MousePointer = vbDefault<br>

 

[shaminda]

thanks reklaw the code works. Now the next problem is formating the data. I'm exporting the data to excel because I can email it to all the employees, and Excel is the only and best software all employees have access to. <br><br>How do I change the font size and the feild size so all the data fits on one page when I print it.

 

[rekclaw]

This is something that I can never remember how to do, but there is an easy way to accomplish your task.&nbsp;&nbsp;Turn on the macro recorder, reformat the data to look the way you want it to look, then turn off the recorder and look at the code that was generated for you.&nbsp;&nbsp;It has been my experience that the range references are a little difficult to deal with so you may need to translate that into something that is more meaningful for you.&nbsp;&nbsp;The generated code should give you a good idea of where to go from there.