Access to Excel VBA

[jshep120]

Good morning all,
I have been attempting to write a rather complicated (for me at least) VBA code that exports data from fields in an Access Form to an Excel Spreadsheet. The form has many entries, as such my ultimate goal is to be able to navigate to each record, click the event button and have Access export the record to the spreadsheet, entering the data in the next empty cells/columns. I have the code that selects which sheet in the workbook to used based on a 'Rel_Date' field in the form. The issue I am having is that clicking the event button works for one record in the form, but when I navigate to the next record, the code opens the Spreadsheet again (via the file path) rather than just add the data to the correct sheet. I am forced to save the workbook, close it and then click the event button again. The sheets also only support 2 entries; once the event button is used thrice, it overwrites the second record in the sheet. Additionally, the data is being added to the bottom of the table in the sheet rather than the top of table, then populating the data below it. I've pieced together the following code from bits of online help and the greatest minds within my organisation and have gone bust! Any help would be greatly appreciated. If my description of my grievances is unclear (likely), please feel free to ask questions! Thank you all.

Private Sub Command23_Click()
Dim objXLApp As Object
Dim objXLBook As Object
Dim i As Integer
Dim tempstr As String
Dim tempSheet As Worksheet
Form_TrackApprovals.RelDate.SetFocus "Form_TrackApprovals is the name of the Form"
tempstr = Form_TrackApprovals.RelDate.Value "This is the function that directs the data to the appropriate sheet in the workbook, based on the 'Rel_Date' field in the Form"
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open( _
\\Usrdata3.highmark.com\home3dir1\LID9P9B\Approval Tracker Proj\Approval tracker\MNINAppTrPractice.xlsx) "This is the file path to the workbook, saved on a local drive for now"
objXLApp.Application.Visible = True
Set tempSheet = objXLBook.Sheets(tempstr)
With objXLBook.Sheets(tempstr)
tempSheet.Select

i = .Range("A2").End(xlDown).Row + 1 "This line of code appears to be...wrong. It does not resolve when entered; it was copied and pasted from a different forum"


.Cells(i, 1).Value = Form_TrackApprovals.STI__ITI__or_Other_
.Cells(i, 2).Value = Form_TrackApprovals.Project_Manager
.Cells(i, 3).Value = Form_TrackApprovals.DocTitle1
.Cells(i, 4).Value = Form_TrackApprovals.Sender_Name
.Cells(i, 5).Value = Form_TrackApprovals.Due_Date
.Cells(i, 6).Value = Form_TrackApprovals.Created
.Cells(i, 7).Value = Form_TrackApprovals.Subject
End With
Set tempSheet = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing
End Sub

 

[JHB]

The easy way is to save and close the Excel-sheet in the end of your code.
Else you've to check if the correct sheet is open and declare some global variable + rewrite your code.

 

[jshep120]

Thanks, JHB. That definitely worked. However I am still running into the issue wherein after 2 records are exported to the appropriate sheet (the setFocus line of code assures this) the third simply overwrites the second rather than continue to populate the table in the sheet. And the records are populating at the bottom of the table as opposed to the top. Thoughts?

 

[JHB]

... And the records are populating at the bottom of the table as opposed to the top.

Yes then your code tells it to insert the data at the end.

[COLOR=Red][B]'Find the last row and add one.[/B][/COLOR]
[B]i = .Range("A2").End(xlDown).Row + 1 [/B]
.Cells([B]i[/B], 1).Value = Form_TrackApprovals.STI__ITI__or_Other_

If you want it at the top, you have to insert a new row/line in the Excel sheet at the top, to prevent data from getting overwritten.

 

[jshep120]

That worked wonderfully, I thank you!
I ended up using
i = 2
.Range("A2").EntireRow.Insert
to insert a new row and now everything is Bristol. I appreciate all of your assistance.

 

[JHB]

You're welcome, good luck.