Question Need help on access-excel relation

wind20mph

MS Access User Since 1996
Local time
Today, 17:03
Joined
Mar 5, 2013
Messages
50
basically, i wanted to post a data from an msaccess to ms excel using msaccess 2007 and ms excel 2007.

first question is, i wanted to post the main form data into formatted excel and the sub-form data below just like the data appearing from the main form

here is the msaccess data:

f0pjFStlp


and here is the formatted excel data:
f06Ug9oPp


how am i gonna extract the data from ms access to post into the example above.

any guide, code or tip would be very much appreciated. my ms access level is just beginner... so please get easy on me.

thanks in advance.
 
thank you for the immediate reply...

what i meant with posting data from ms access form, is the specific fields on ms access form into specific cells in ms excel. and to add on that condition, i would like to learn how to paginate the subform datasheet into excel file (e.g. counting first number of rows in a query, then posting them all into excel cells).

the idea is to post a data from ms access into a formatted msexcel worksheet. example fldName to excel cell (A11) fldDesignation to excel cell (O11) and below this lines are 12 rows with date, othrs, abs,remaks and loans to excel cell (A15, G15, P15, T15, AA15 up to A27, G27, P27, T27, AA27).

of this case, how do i make the loop, which condition should i use and if clearly, i would like to learn how to code it and post it by not exporting.

again, thanks in advance.
 
Why do you want to insert the data in Excel, everything you show in the Printscreen can be done in MS-Access, so ...??
 
Did you look at the link I provided? There should be enough there to get you started.
 
Why do you want to insert the data in Excel, everything you show in the Printscreen can be done in MS-Access, so ...??
there is a customized format in excel. that is why i am exporting it to a formatted excel.

yes its true that you can do it in msaccess but we are submitting the form in excel format.
 
Did you fix it by following the link GinaWhipp gave you?
 
You are trying to use Excel for what it was not originally built for, while you can easily create this report in Access.
Lines 1-8 in Excel are some kind of TopForm, and are not a real spreadsheet data.

If you are submitting the data to be manipulated by someone else export it as unformated data.
If you are exporting it to someone else to look at export it as PDF.

As you export it now you'll have to go line by line, point by point to export the data and format it.
 
You are trying to use Excel for what it was not originally built for, while you can easily create this report in Access.
Lines 1-8 in Excel are some kind of TopForm, and are not a real spreadsheet data.

If you are submitting the data to be manipulated by someone else export it as unformated data.
If you are exporting it to someone else to look at export it as PDF.

As you export it now you'll have to go line by line, point by point to export the data and format it.

ok let me clarify things.

the link that GinaWhipp gave me has something to do with basic exporting data. yes it does help. a little by teaching me how to communicate with excel from VBA code in an ms access button.

i was looking for a code that already made iin VBA with MSExcel as object reference. the data i was looking for is in a sample posted above. however, i can't find an exact VBA code that will give me the output i was asking. so i posted in a general section of this forum.

simply, i have seen a VBA-MSAccess and Excel together giving that exact data in a formatted msexcel file by just clicking one button in a msaccess form. trying to extract the VBA module, i find it encrypted with password and i cannot see how it was done.

further, it is used by several programmers, to give a good excel report on a government forms using msaccess but they do not share the code.

i find some references online but i got the error ("Excel was not saved"). but when you run it in visible mode, you can see everything in the field as posted. code is below:

Code:
Option Compare Database

Sub ExportRecordsetToExcel()
On Error GoTo Err_ExcelErr

'Declare Variables
Dim AppExcel As New Excel.Application
'Enter the Excel Path
Const hbkExcelApp = "D:\Data\devs\mnt\EquipmentHistory.xlsx"
'Target Results Folder
Const hbkExelPath = "D:\Data\devs\mnt"
Dim WB As Object
Dim i As Integer
Dim n  As Integer
Dim strQVehicle As String
Dim strQHist As String
Dim mydb As DAO.Database
Dim rsV As DAO.Recordset
Dim rsH As DAO.Recordset


strQVehicle = "SELECT * FROM eqpt WHERE eid = '" & Me![eid] & "'"
strQHist = "SELECT * FROM hist WHERE eid ='" & Me![eid] & "'"

Set mydb = CurrentDb
Set AppExcel = CreateObject("Excel.Application")
Set rsV = CurrentDb.OpenRecordset(strQVehicle)
Set WB = AppExcel.Workbooks.Open(hbkExcelApp)
WB.Worksheets("sheet1").Range("K8") = rsV("ename")
WB.Worksheets("sheet1").Range("K9") = rsV("eid")
WB.Worksheets("sheet1").Range("C14") = rsV("make")
WB.Worksheets("sheet1").Range("O14") = rsV("snpln")
WB.Worksheets("sheet1").Range("AB14") = rsV("brand")
WB.Worksheets("sheet1").Range("C16") = rsV("model")
WB.Worksheets("sheet1").Range("O16") = rsV("dp")
WB.Worksheets("sheet1").Range("AB16") = rsV("supplier")
WB.Worksheets("sheet1").Range("C18") = rsV("trund")
WB.Worksheets("sheet1").Range("O18") = rsV("trunby")
WB.Worksheets("sheet1").Range("AB18") = rsV("trunres")
WB.Worksheets("sheet1").Range("C20") = rsV("refrences")

For i = 27 To n
'Enter your data from your table here to the required cells
WB.Worksheets("sheet1").Range("C" & i) = rsH("hdate")
WB.Worksheets("sheet1").Range("H" & i) = rsH("rsrn")
WB.Worksheets("sheet1").Range("M" & i) = rsH("htype")
WB.Worksheets("sheet1").Range("Q" & i) = rsH("activities")
WB.Worksheets("sheet1").Range("AC" & i) = rsH("sprrep")
WB.Worksheets("sheet1").Range("AI" & i) = rsH("expinc")
'Repeat this line for each piece of data you need entered
'Changing the Sheet name, cell range, a field name as per your requirements
'WB.Wor...
'WB.Wor...

rsH.MoveNext
Next i
i = i + 1
Loop

Dim saveAsStr As String
saveAsStr = hbkExelPath & ("hist1") & ".xlsx"
WB.SaveAs = saveAsStr
WB.Close
Set WB = Nothing
AppExcel.Quit
Set appecel = Nothing

Exit_ExcelErr:
    Exit Sub

Err_ExcelErr:
MsgBox Err.Description
Resume Exit_ExcelErr

'Clear All Data in the memory bank
Set mydb = Nothing
Set rsV = Nothing
Set rsH = Nothing
End Sub
Private Sub excelpostcmd_Click()
    ExportRecordsetToExcel
End Sub
 
Last edited:
The link GinaWhipp gave you is exectly what you need.
As I told you, since you dont export the query data youll have to do the top part point-by-point.
 
Hmm,

1. You will not find the *exact* code because one usually writes\rewrites the generic code to suit their needs and no two persons have the same requirements.

2. Once you configure the code to suit your needs you can put a button on your Form and Call the code from it. And then, yes, the code will execute with the pressing of a button.

3. Do you have this code in a separate Module or in the Module behind the Form?

4. To root out errors in your code add...

Code:
Option Explicit

... under Option Compare Database. Then, from the Menu select Debug > Compile YourDatabaseName and fix any errors it finds.

5. Also, try changing this...

Code:
saveAsStr = hbkExelPath & ("hist1") & ".xlsx"
...to this...

Code:
saveAsStr = hbkExelPath & ("hist1") & ".xlsx", 51

6. I would also remove the lines I indicated below...

Code:
saveAsStr = hbkExelPath & ("hist1") & ".xlsx"
WB.SaveAs = saveAsStr
WB.Close  [B]REMOVE[/B]
Set WB = Nothing [B]REMOVE[/B]
AppExcel.Quit
Set appecel = Nothing

...As you are already saving and closing, you can't Quit Excel when it seems there is nothing to quit. Hmm, did I say that right, well someone will correct me if I didn't.

Finally, you keep referencing images in your first post and again, I see no images which is why code not *help you out* with code that more closely matched what you wanted. My crystal ball is, once again, on the fritz! :D
 
thank you very much GinaWhipp you got my point and i am debugging the codes. i have eliminated the following:

Code:
saveAsStr = hbkExelPath & ("hist1") & ".xlsx" WB.SaveAs = saveAsStr WB.Close  Set WB = Nothing  AppExcel.Quit Set appecel = Nothing
however adding this:
Code:
saveAsStr = hbkExelPath & ("hist1") & ".xlsx", 51
doesn't seem to work. so i removed the 51
and still do not save as new file name.

i am posting the msaccess file and msexcel file together. hoping some enlightenment. again thank you very much.
 

Attachments

Last edited:
I will review this tomorrow... if no one gets here first!
 
There are some code errors, (a lot).
You are missing a "\":
Code:
Const hbkExelPath = "D:\Data\devs\mnt[B][COLOR=Red]\[/COLOR][/B]"
In the for next loop you missing Step -1.
Code:
For i = 27 To n [B][COLOR=Red]Step -1[/COLOR][/B]
You are not setting the recordset variable rsH to a table/query.
The correct syntax for SaveAs:
Code:
WB.SaveAs saveAsStr
and not 
WB.SaveAs[B][COLOR=Red] = [/COLOR][/B]saveAsStr
A good advice is to comment out the errorhandling until your code runs perfect and also putting breakpoints in your code, so you see how your code execute.
 
thank you for the clarification.
i have revised the code and added the Microsoft ActiveX Object 2.8 to references.

I got it. and it saved the file perfectly. thank you very much for the helping hand. This Problem is now solved.

here is my revised code:
Code:
Option Compare Database
Option Explicit



Sub ExportRecordsetToExcel()
On Error GoTo Err_ExcelErr

'Declare Variables
Dim AppExcel As New Excel.Application
'Enter the Excel Path
Const hbkExcelApp = "D:\Data\devs\mnt\EquipmentHistory.xlsx"
'Target Results Folder
Const hbkExelPath = "D:\Data\devs\mnt\"
Dim WB As Object
Dim i As Integer
Dim n  As Integer
Dim strQVehicle As String
Dim strQHist As String
Dim mydb As DAO.Database
Dim rsV As DAO.Recordset
Dim rsH As DAO.Recordset


strQVehicle = "SELECT * FROM eqpt WHERE eid = '" & Me![eid] & "'"
strQHist = "SELECT * FROM hist WHERE eid ='" & Me![eid] & "'"

Set mydb = CurrentDb
Set AppExcel = CreateObject("Excel.Application")
Set rsV = CurrentDb.OpenRecordset(strQVehicle)
Set WB = AppExcel.Workbooks.Open(hbkExcelApp)
WB.Worksheets("sheet1").Range("K8") = rsV("ename")
WB.Worksheets("sheet1").Range("K9") = rsV("eid")
WB.Worksheets("sheet1").Range("C14") = rsV("make")
WB.Worksheets("sheet1").Range("O14") = rsV("snpln")
WB.Worksheets("sheet1").Range("AB14") = rsV("brand")
WB.Worksheets("sheet1").Range("C16") = rsV("model")
WB.Worksheets("sheet1").Range("O16") = rsV("dp")
WB.Worksheets("sheet1").Range("AB16") = rsV("supplier")
WB.Worksheets("sheet1").Range("C18") = rsV("trund")
WB.Worksheets("sheet1").Range("O18") = rsV("trunby")
WB.Worksheets("sheet1").Range("AB18") = rsV("trunres")
WB.Worksheets("sheet1").Range("C20") = rsV("refrences")

Set rsH = CurrentDb.OpenRecordset(strQHist)
n = rsH.RecordCount
For i = 27 To n
'Enter your data from your table here to the required cells
WB.Worksheets("sheet1").Range("C" & i) = rsH("hdate")
WB.Worksheets("sheet1").Range("H" & i) = rsH("rsrn")
WB.Worksheets("sheet1").Range("M" & i) = rsH("htype")
WB.Worksheets("sheet1").Range("Q" & i) = rsH("activities")
WB.Worksheets("sheet1").Range("AC" & i) = rsH("sprrep")
WB.Worksheets("sheet1").Range("AI" & i) = rsH("expinc")
'Repeat this line for each piece of data you need entered
'Changing the Sheet name, cell range, a field name as per your requirements
'WB.Wor...
'WB.Wor...

rsH.MoveNext
Next i
i = i + 1

'Clear All Data in the memory bank
Set mydb = Nothing
Set rsV = Nothing
Set rsH = Nothing

Dim saveAsStr As String
saveAsStr = hbkExelPath & ("hist" & Format(Now(),"mmddyyyyhhmm")) & ".xlsx"
WB.SaveAs saveAsStr
WB.Close
Set WB = Nothing
AppExcel.Quit
Set AppExcel = Nothing

Exit_ExcelErr:
    Exit Sub

Err_ExcelErr:
MsgBox Err.Description
Resume Exit_ExcelErr
End Sub



Private Sub excelpostcmd_Click()
    ExportRecordsetToExcel
End Sub
 
Last edited:

Users who are viewing this thread

Back
Top Bottom