Open Excel Workbook in same folder as Access Database using CurrentProject.Path

Hi There -
I have a code snippet that is not working when I try to use CurrentProject.Path from MsAccess to point to a pre made
Excel Workbook and place data in it using CopyFromRecordset. It has to be something simple I'm missing here.
Thanks in advance,
GeneBat
 (see code below) Cap if you're out there I need help. :)
 
Public Function ExcelfromAccess()
    
    On Error Resume Next

If MsgBox("Are You Sure You Want to Export This data to Excel?", 289, "Export to Excel") = vbCancel Then
        
        Exit Function
   
    Else
    'open excel
        Dim AppExcel As Excel.Application
        Set AppExcel = New Excel.Application
        'Dim strFileName As String
        
    'open new workbook
        Dim oWB As Excel.Workbook
        Dim sFullPath As String
        
        sFullPath = CurrentProject.Path & "\UcaasTemplate.xlsx"

'        Set oWB = AppExcel.Workbooks.Add("UcaasTemplate.xlsx") 'you can specify a template here
'        strFileName = Application.CurrentProject.Path & "\UcaasTemplate.xlsx"
'        Set oWB = AppExcel.Workbooks.Add(Template:="C:\Users\xtek109\Documents\Ucaas Project\UcaasTemplate.xlsx") 'template name goes here + this works
        AppExcel.Visible = True
        AppExcel.Workbooks.Open (sFullPath)
    'copy data to Excel
        oWB.Worksheets(1).Range("A5").CopyFromRecordset CurrentDb.OpenRecordset("LdapCutsheetFormat", dbOpenDynaset)
        
    'turn excel over to user control
        AppExcel.Visible = True
        AppExcel.UserControl = True
        Set AppExcel = Nothing
        Set oWB = Nothing
End If

End Function 

Open in new window

GeneBatAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertFabrice LambertCommented:
hi,

the workbooks.open method return a reference to the opened workbook, wich you arn't retrieving.
set oWb =  AppExcel.Workbooks.Open(sFullPath)

Open in new window

0
GeneBatAuthor Commented:
Hi Fabrice -
I don't get it. When I run my code it opens a empty workbook. I'm try to put data into a pre-made workbook/sheet.
Thanks, GeneBat
0
Fabrice LambertFabrice LambertCommented:
How is your workbook object (oWb) instanciated ?
As it stand in your code, the 2 lines likely to do it are commented out.

Plus, ensure the path is correct, and the pre-made excel document is the desired one.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

GeneBatAuthor Commented:
Hi There -
My path for my workbook is in the same directory folder as my Access database. I'm trying to open it and populate it? So, I should uncomment those 2 lines commented and it should work, it doesn't the workbook opens up blank for now.
0
Fabrice LambertFabrice LambertCommented:
I have no better suggestion than my first answer.

Toggle a breakpoint, execute step by steps (F8 hotkey), check your variables values in the spy window ect ..... basic debug stuffs.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GeneBatAuthor Commented:
After doing a step through and using your first suggestion it works as expected.
Thanks, Fabrice
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.