Quick loop from Access to Excel ? 1

[rudo]

I have an Access Database, which at each start of worksession has to be transferred to an existing Excel-sheet. I am not a specialist of code for Access, and my present solution takes 10 minutes for 1 database of 600 recordsets with 150 fields. I suppose ther must be a quicker way. (Order on Excelsheet = order of records.)

Does anyone know code for a quick loop?

Thank you in advance

Rudo
(Holland)

 

[ETID]

You would not need to export the data to excel, (too many steps)...

Create a live link to the table or query from within excel,

From excel click Data>Get External Data>New Database Query>....the choose msaccess databases* from the list...follow the wizard from there (to select your mdb, table or query)
then return your data to excel.

You can opt to use the wizard or MsQuery (a gui type interface, similar to access)at this point...
after your comfy with this...I would recommend the MsQuery option.


when data is returned,...you can right click in the data and select properties to refresh on open...auto fill formulas etc.

this is now a live link, any time the workbook is opened. your current / up to date access data comes in (if you set the refresh on open option)

 

[rudo]

To ETID

Thank you for you suggestion. I didn't get it working yet, but I suppose your solution doesn't update automatically the Excelworksheet with the data from Access. User needs Excelsheet, as it is part of a complex existing vb Excel application. It has to be updated at each start of worksession from an .mdb file.

The code I use:

sel ml = Workbooks("Articles&quot.Sheets("Sheet1&quot
set 0MyDB = Workspaces(0).OpenDatabase("Articleslist&quot
Set 0MyDbrs = 0MyDB.OpenRecordset("mailist", dbOpenDynaset)

With 0MyDbrs
.MoveFirst
For i = 1 to 10 'first 10 are empty
.MoveNext
Next i
r = 11 'data on XL-sheet start on row 11
'Fields are named F1, F2 etc
Do while not .EOF
For k = 1 to 155
ml.Cells(r,k)= 0MyDbrs.Fields("F" & k)
Next k
.Move Next
r = r + 1
Loop
End With

Would ther be a quicker way?

Thanks for your suggestions.

Greetings from Holland,
Rudo

 

[ETID]

Yes...the sheet will update upon open if you set the propertie.


read the procedure again....

---------------------------



when data is returned,...you can right click in the data area and select properties to refresh on open...auto fill formulas etc.

this is now a live link, any time the workbook is opened. your current / up to date access data comes in (if you set the refresh on open option)

 

[VulcanJedi]

I agree w/ ETID this would be more practical? I have done this, and no really you wouldn't need any code, but you could write code to specifically identify intervals / events to update. But I've done this to use pivot tables and the like, seemingly works great. Can Import Statically (one time only) or link (changes in XL, change in Access and vice versa) or link dynamically and have auto updated data from Acess QRY you can filter and everything you want to do from access straight from XL. Tranpkp
************************************
- Let me know if this helped/worked!

 

[cchanslor]

Do I understand that you are bringing your records over to Excel one record at a time? If so, there is a much quicker way to do this... You can pull a recordset of the access table then dump this straight to excel. If you want, I have some code I could share to show you how I do this.

 

[VulcanJedi]

Cchanslor I believe I may be interested in you educating me on that perhaps, if you would! Tranpkp
************************************
- Let me know if this helped/worked!

 

[ETID]

.....so why would you write code when there is a built in procedure to do this? Maybe I'm missing the concept.

 

[cchanslor]

Here is the method I use to dump a large (or small) amount of data to Excel from Access. The only time I do not use this is when I am only putting data into a single cell.

Note: The way my code is written, I operate on a single sheet at a time. If you use the Workbook object, you will need to alter the code to specify workbook and sheet.
--------------------------------------------------

Dim rstEscalations As Recordset

Set dbs = CurrentDb
Dim strQDFEscalationsMTD As String

'Define SQL for recordset
strQDFEscalationsMTD = "SELECT " & strMain(pcLinkName) & ".PROBLEM_ID, " & _
                                   strMain(pcLinkName) & ".Escalate_date, " & _
                                   strMain(pcLinkName) & ".HRID, " & _
                                   strMain(pcLinkName) & ".nloc, " & _
                                   strMain(pcLinkName) & ".city_name, " & _
                                   strMain(pcLinkName) & ".state, " & _
                                   strMain(pcLinkName) & ".SEVERITY, " & _
                                   strMain(pcLinkName) & ".SYSTEM, " & _
                                   strMain(pcLinkName) & ".COMPONENT, " & _
                                   strMain(pcLinkName) & ".ITEM, " & _
                                   strMain(pcLinkName) & ".MODULE, " & _
                                   strMain(pcLinkName) & ".PROBLEM_ABSTRACT, " & _
                                   strMain(pcLinkName) & ".DESCRIPTION " & _
                        "FROM " & strMain(pcLinkName) & " " & _
                        "WHERE (((Month([" & strMain(pcLinkName) & "]![Escalate_date])) = " & Month(Date) & ")) " & _
                        "ORDER BY " & strMain(pcLinkName) & ".PROBLEM_ID, " & strMain(pcLinkName) & ".Escalate_date;"

Set rstEscalations = dbs.OpenRecordset(strQDFEscalationsMTD, dbOpenForwardOnly)

Call WSgxsDumpQDFtoSheet(xlSheet, rstEscalations, 1, 1, True)

Set rstEscalations = Nothing
Set dbs = Nothing

-----------------------------------------------------

Here is how I dump the data to Excel:

NOTES:
[ol][li]lngRowPlacement & lngColPlacement - cell location to "dump" data. IE: if I want to dump this recordset to cell g15 then row = 15, col = 7[/li]
[li]bolUseHeaders - if I want to include the field names from my recordset the answer is True, if not, false. Note that if you include headers the headers are placed at row & col settings, then data dumped immediately beneath.[/li][/ol]

--------------------------------------
Sub WSgxsDumpQDFtoSheet(xlapp As Excel.Worksheet, rst As Recordset, lngRowPlacement As Long, lngColPlacement As Long, bolUseHeaders As Boolean)

Dim lngCols As Long
Dim lngRows As Long

xlapp.Activate
'xlapp.Worksheets(worksheet_name).Select

lngRows = lngRowPlacement
If bolUseHeaders = True Then
For lngCols = 0 To rst.Fields.Count - 1
xlapp.Cells(lngRows, lngColPlacement + lngCols) = rst.Fields(lngCols).Name
Next lngCols
lngRows = lngRows + 1
End If

xlapp.Cells(lngRows, lngColPlacement).CopyFromRecordset rst

Set rst = Nothing

End Sub
------------------------------------------

 

[ETID]

.....Again, so why would you write code when there is a built in procedure to do this? Maybe I'm missing the concept. Is there some advantage to doing it this way?

 

[cchanslor]

Answer to ETID:

I prepare numerous reports each morning all from within Access. Preferred delivery to my users is Excel. By using this method, I can control everything from Access and it uses fewer resources. Plus since I mail out reports, I don't have to try to get around users not having the source doc and accidentally choosing to update... fewer headaches and phone calls for me to deal with.

I tend to use the connection method when the files will stay on my desktop.

 

[VulcanJedi]

so this technique is still static no? The code only updates the data once and you disseminate right? Tranpkp
************************************
- Let me know if this helped/worked!

 

[ETID]

Cool thanks

 

[cchanslor]

to tranpkp:

yes

 

[rudo]

To ETID

Thanks for yor reaction. Your method works great. However, I have titles that would have to be inserted on the Excel-sheet after each update. Normally the macros do the work. I didn't succeed to include the query in a macro. Probably too much data to select.

Would you know if there is a way to start a macro automatically immediately after each update?

And another question: Is there a similar easy way to export the data to Access?

You have been of great help so far. Thank you very much.

Rudo
(Holland)

 

[rudo]

To cchanslor

Thank you very much for the code. The static solution you discribe is exactly what I was searching for. However, the dynamic method Etid explained could be an improvement for the users. I am not sure if I can implement the dynamic solution as easily as the static one. Adaptions would have to be made to a lot of existing macros. Still have to analyse implications.

Many thanks for your code!!!

Rudo