Populate data in different 'Sheets' in Excel from Access

[wavp]

Currently I populate one excel spreadsheet with data from lots of different tables.

What I would like to do is say....populate one set of data into Sheet 1 and then change to a another Sheet...say Sheet 2 keeping my connection to that workbook open when I do this.

Is there away?

Cheers Will

eg:

Set objXL = CreateObject("Excel.Application") 'Start application
Set objXLWrkBk = objXL.workbooks.Open(strSaveAs) 'XLS file which will be used

Set objXLWrkSht = objXLWrkBk.worksheets("Sheet2")

  
'Populate cells for Sheet 1 then switch to Sheet 2

Set objXLWrkSht = objXLWrkBk.worksheets("Sheet2")

'Populate cells for Sheet 2

 

[fuzzygeek]

Pop 2 sheets

You can do three if you wish. Example of working code that I use:

Set objXL = New Excel.Application
Set wbXL = objXL.Workbooks.Add
Set wsXLSin = wbXL.Worksheets(1)
Set wsXLCos = wbXL.Worksheets(2)

'Delete the third(default) worksheet
wbXL.Worksheets(3).Delete

Then you can rename the sheets anything you wish.

'Set the name of the two worksheets
wsXLSin.Name = "Sin"
wsXLCos.Name = "Cos"

 

[spilwayboy]

This code is very helpful to what I am doing as far as switching sheets, but is it possible that one of you guys would mind posting the code that actually picks up the table values and carries it to excel? That is what I am looking to do also besides switching sheets. Thank you for all of the help and answers to my questions before I ask them.

Regards,
spil

 

[dcx693]

Which part of the code are you having trouble with? Picking up the values from the table(s) or placing them onto the sheets in Excel?

 

[spilwayboy]

Both parts if you do not mind, I can change the code to suit my table mnames and stuff but do not know how to start on the code, but can understand how to switch sheets.

Thanks,
Spil

 

[dcx693]

OK, let's start with getting data out of the Access tables. Do you know how to work with recordsets using the DoCmd.OpenRecordSet command?

 

[spilwayboy]

I do not, but when I see a piece of code I can figure out what it does and how to change it to suit my needs but not really start from a blank tablet. I have some small code pieces that export to bookmaks in word if that is the same procedure. I am not sure though. This is the code I use for that

Private Sub Option1_Click()
' Open a Fax Cover Sheet in Word and insert text - used by menu command.

Dim dbs As DAO.Database
Dim rstMergeThese As Recordset
Dim oApp As Object

Set oApp = CreateObject("Word.Application")
oApp.Visible = True

With oApp
.Documents.Open "Z:\Word Templates\Fax Cover Metairie.doc"
' Move to each bookmark and insert text from the form.
.ActiveDocument.bookmarks("CompanyName").Select
.Selection.Text = (CStr(Forms!company!CompanyName))
.ActiveDocument.bookmarks("CompanyFaxNumber").Select
.Selection.Text = (CStr(Forms!company!Locationsubform!CompanyFaxNumber))
.ActiveDocument.bookmarks("FirstName").Select
.Selection.Text = (CStr(Forms![company]!Locationsubform![Contacts]![FirstName]))
.ActiveDocument.bookmarks("LastName").Select
.Selection.Text = (CStr(Forms![company]!Locationsubform![Contacts]![LastName]))
.ActiveDocument.bookmarks("InsertText").Select
'if word isn't running, start and activate it
If Err Then
Shell "C:\Program Files\Microsoft Office\Office\" & "WinWord / Automation", vbMaximizedFocus
AppActivate "Microsoft Word"
End If

Set oApp = Nothing

End With

End Sub

Is it the same procedure for excel?

Thanks,
Spil

 

[dcx693]

Well, it's good that you've got these lines:

Dim dbs As DAO.Database
Dim rstMergeThese As Recordset

but they're not used anywhere in the code! You can just remove them and the program will run fine without them.

What you really need to do is to get yourself a good Access book like Access 2000 (or 2002) Developer's Handbook by Ken Getz, Paul Litwin, and Mike Gilbert.

Try to search the Access help for information on using the OpenRecordSet command.

I'll at least give you the major steps involved. As you get to each step, try figuring out what you need to do. Post your progress and I, or someone else, will give you additional pointers on proceeding.

1 - Decide what tables you want to pass to Excel.
2 - Use the DoCmd.OpenRecordSet command to open each one in turn.
3 - Loop through the recordsets, field by field and record by record, copying the content to your desired Excel. Use the rst.Fields collection in Access and the Cells(x,y) collection in Excel to do that.

It might be easier in the end to use the DoCmd.TransferSpreadsheet command to simply export the tables to individual xls files, then merge those into one workbook from Excel.

 

[spilwayboy]

Thanks for the help and direction I will give it a try.

Regards,
Spil

 

[jenvandiver]

I have been working on something similar, importing data from Access tables into Excel. This is how I did it...

In a blank Excel worksheet, go to Data, then Get External Data, then choose New Database Query. From the database list, choose MS Access Database. Click OK, then select your specific database and click OK. Make sure your database name does not contain any periods (.) other than in the file extension name. For example, if your db is named 123.mdb, that will work fine. But if it's named 1.2.3.mdb, it won't work and you'll need to rename it without the periods.

Choose the columns you want included in Excel from the tables. Click Next and enter any conditions for filtering and sorting data. In the Finish box, make sure to save your query before you return the data to Excel if you want to use it again.

Excel will ask you for the data range, just click on the cell, columns or rows where you want the data to start and it will enter everything in. If you want the data to refresh every time the workbook is opened, click a cell inside the range and go to Data, Get External Data, Data Range Properties. Under Refresh Control, click Refresh Data on File Open.

This should work - it did for me!

 

[spilwayboy]

Thank you very much for the reply. That will help greatly.

Spil

 

[George Too]

My two cents... This is a fraction of the the code that I use which was taken from various sources in this forum. Hope it helps.

************************************************
'Open and populate Excel File

'File name
strFilePathName = Me.txtPath & Me.txtProject & " - COA"

strFileToOpen = Me.childPaths.controls!cmbPath2

Set ExcelApp = CreateObject("Excel.Application")

With ExcelApp
.Workbooks.Open strFileToOpen
.Visible = True
.range("I5").Select
.Activecell = Me.cmbPress
.range("I6").Select
.Activecell = Me.txtProject
.range("L14").Select
.Activecell = Me.txtWidth
.range("L15").Select
.Activecell = Me.txtRepeat
.range("S15").Select
.Activecell = Me.txtMinRepeat
.range("T15").Select
.Activecell = Me.txtMaxRepeat

'Save the results.
.ActiveWorkbook.SaveAs FileName:=strFilePathName
'Close and quit
.ActiveWindow.Close
End With
ExcelApp.Quit
Set ExcelApp = Nothing

************************************************

Regards,
George

 

[spilwayboy]

That also helps thanks for the code.

Spil