UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Copying Access Field To Excel Doc, Access 2016    
 
   
hbacchiocchi
post Feb 13 2018, 12:28 PM
Post#1



Posts: 4
Joined: 15-July 16



Hi All,

I have an access database with fields that I am trying to copy into an Excel Doc. I wrote the first chunk of code to open an Excel Template, copy the fields onto the Excel doc and then save the doc with a specific name based on the fields, and it works perfectly! (I'm relatively new to VBA and this is the first thing I've written mostly by myself opposed to copy/pasting from someone else, so I'm super excited about my success, lol)

CODE
Private Sub Command89_Click()
Dim objXLApp As Object
Dim objXLBook As Object
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("Z:\SATELLITES\17-18\Database\NotesTemplate.xltm")
objXLApp.Application.Visible = True

objXLBook.ActiveSheet.Range("C1") = Me.FirstName
objXLBook.ActiveSheet.Range("D1") = Me.LastName
objXLBook.ActiveSheet.Range("C2") = Me.DOB
objXLBook.ActiveSheet.Range("C3") = Me.Zip
objXLBook.ActiveSheet.Range("C4") = Me.Email
objXLBook.ActiveSheet.Range("C5") = Me.Phone
objXLBook.ActiveSheet.Range("E2") = Me.Grade.Column(1)
objXLBook.ActiveSheet.Range("A7") = Me!NewVisitSub.Form.DateOfService
objXLBook.ActiveSheet.Range("D7") = Me!NewVisitSub.Form.Notes
objXLBook.ActiveSheet.Range("E7") = Me!NewVisitSub.Form.Advisor.Column(1)

objXLBook.SaveAs ("Z:\2017-2018 Notes\178 -1718\" & Me.LastName & ", " & Me.FirstName & " (" & Me.Grade.Column(1) & ") " & Me!NewVisitSub.Form.Satellite.Column(1) & "")

Me.FileName = "Z:\2017-2018 Notes\178 -1718\" & Me.LastName & ", " & Me.FirstName & " (" & Me.Grade.Column(1) & ") " & Me!NewVisitSub.Form.Satellite.Column(1) & ".xlsx"
End Sub


I'm now trying to write a code to open the specific file and copy additional information (fields: "Me!NewVisitSub.Form.DateOfService", "Me!NewVisitSub.Form.Notes", "Me!NewVisitSub.Form.Advisor.Column(1)") into the next empty row (in columns "A", "D", and "E" respectively). Unfortunately, I cant code to a specific cell as I did above because the file may be added to outside of Access, as it is a shared file, and I don't want any information overwritten by mistake.

Here is what I have so far:
CODE
Private Sub Command108_Click()
Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook

Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open(Me.FileName)
objXLApp.Application.Visible = True

Dim lastrow As Long

lastrow = Sheets("Sheet1").Range("A65536").End(xlUp).Row

objXLBook.ActiveSheet.Cells(lastrow, 1) = Me!AddVisitSub.Form.DateOfService
objXLBook.ActiveSheet.Cells(lastrow, 4) = Me!AddVisitSub.Form.Notes
objXLBook.ActiveSheet.Cells(lastrow, 5) = Me!AddVisitSub.Form.Advisor.Column(1)
End Sub


So this is where I'm stuck. The code opens the Excel Doc perfectly, but does not copy to the next empty row. It instead copies to row 7 and copies over the fields copied in the first set of code ("A7", "D7", and E7").

Any advice or help is greatly appreciated!
Go to the top of the page
 
theDBguy
post Feb 13 2018, 12:44 PM
Post#2


Access Wiki and Forums Moderator
Posts: 72,377
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

Is the name of the active sheet "Sheet1?" If not, then try replacing your code to use the correct name.

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
hbacchiocchi
post Feb 13 2018, 01:01 PM
Post#3



Posts: 4
Joined: 15-July 16



Yes, I was not the creator of the template (and it is used in other applications) so I'm pretty stuck with "Sheet1"
Go to the top of the page
 
theDBguy
post Feb 13 2018, 01:09 PM
Post#4


Access Wiki and Forums Moderator
Posts: 72,377
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Just to make sure you understood what I was saying... In your code to check for the last row, you specified "Sheet1" as the sheet to look for the last empty row. However, in your code to populate the blank rows, you used ActiveSheet to specify the sheet to enter the information. I was just making sure you were referring to the same Excel sheet where you got the info about the last row and the one you are writing data into.

Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
doctor9
post Feb 13 2018, 02:39 PM
Post#5


UtterAccess Editor
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


hbacchiocchi,

Just as a follow-up to theDBguy's advice, here's what I'd go with:

CODE
Dim ws as Object
Set ws = objXLBook.Sheets("Sheet1")


Place the Set bit AFTER setting up objXLBook, of course.

Then, rather than using this reference to the worksheet:
objXLBook.Sheets("Sheet1").

You'd use this:
CODE
ws.


Also, remember that for every object that you set up, you should end your subroutine with a corresponding:

CODE
Set ws = Nothing


Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th June 2018 - 03:39 AM