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?
 
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
 
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
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
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
 
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
 
GeneBatAuthor Commented:
After doing a step through and using your first suggestion it works as expected.
Thanks, Fabrice
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.