Can't Save Target WB

[pnpez]

Hello,
I’m new to vba and the below code works fine to export datato the target workbook. But now tryingto save the target file to the current path plus add “\template” path. I have tried a lot of things but can’t get itto work. Can someone please help me withthe code to do this? It would bemuch-appreciated.

Function ExportAppeals28()



Dim dbs As Database
Dim Path As String, Path1 As String


Path1 = "/NH_Appeals_tbl0028_template.xlsx"
Set dbs = CurrentDb

Set rstable =dbs.OpenRecordset("tbl0028_All_Appeals")

Set excelApp = CreateObject("Excel.application","")
excelApp.Visible = True

Set targetWorkbook =excelApp.Workbooks.Open(CurrentProject.Path & Path1)
targetWorkbook.Worksheets("Sheet1").Range("C2").Co pyFromRecordsetrstable

End Function

[Micron]

Your path string has a forward slash instead of a back slash. Suggest you do yourself and us these favours when posting code:
copy and paste your code to avoid typos (maybe you did that)
use code tags with proper indentation. See how this is easier to read and does not add extra spaces or in some cases, drop characters like \

Function ExportAppeals28()

Dim dbs As Database
Dim Path As String, Path1 As String

Path1 = "\NH_Appeals_tbl0028_template.xlsx"
Set dbs = CurrentDb

Set rstable =dbs.OpenRecordset("tbl0028_All_Appeals")

Set excelApp = CreateObject("Excel.application","")
excelApp.Visible = True

Set targetWorkbook =excelApp.Workbooks.Open(CurrentProject.Path & Path1)
targetWorkbook.Worksheets("Sheet1").Range("C2").CopyFromRecordset rstable

End Function

Why do you not declare four of your object variables here. Have done that at the module level?
Also, look at the copyrecordset part in your original code and compare.

[pnpez]

Your path string has a forward slash instead of a back slash. Suggest you do yourself and us these favours when posting code:
copy and paste your code to avoid typos (maybe you did that)
use code tags with proper indentation. See how this is easier to read and does not add extra spaces or in some cases, drop characters like \

Function ExportAppeals28()

Dim dbs As Database
Dim Path As String, Path1 As String

Path1 = "\NH_Appeals_tbl0028_template.xlsx"
Set dbs = CurrentDb

Set rstable =dbs.OpenRecordset("tbl0028_All_Appeals")

Set excelApp = CreateObject("Excel.application","")
excelApp.Visible = True

Set targetWorkbook =excelApp.Workbooks.Open(CurrentProject.Path & Path1)
targetWorkbook.Worksheets("Sheet1").Range("C2").CopyFromRecordset rstable

End Function

Why do you not declare four of your object variables here. Have done that at the module level?
Also, look at the copyrecordset part in your original code and compare.

Thanks for finding the slash mistake. This is the module level; it's all the code there is. I'm still not sure how to save this target file.

[Welshgasman]

Probably something along the lines of

targetWorkbook.Save

https://www.google.com/search?q=save...hrome&ie=UTF-8

[Micron]

targetworkbook.Save? Or perhaps targetworkbook.Close - cannot recall if you will get a prompt. You didn't say if saving to a new file from a template (or I've missed that) otherwise I would suggest targetworkbook.SaveAs

This is where I go when I need automation refreshers for Excel
http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

This is the module level; it's all the code there is

No, that is at the procedure level. Maybe you mean this code is in a standard module? Module level code exists at the top of the module in its declaration section and lies outside of any procedures. I presume you do not have Option Explicit right after the Option Compare statement otherwise you wouldn't get away with not declaring these object variables within the function. It may work but it's not the best approach. I must be getting rusty because I'm not getting why that code doesn't error out because of it.

[pnpez]

targetworkbook.Save? Or perhaps targetworkbook.Close - cannot recall if you will get a prompt. You didn't say if saving to a new file from a template (or I've missed that) otherwise I would suggest targetworkbook.SaveAs

This is where I go when I need automation refreshers for Excel
http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

No, that is at the procedure level. Maybe you mean this code is in a standard module? Module level code exists at the top of the module in its declaration section and lies outside of any procedures. I presume you do not have Option Explicit right after the Option Compare statement otherwise you wouldn't get away with not declaring these object variables within the function. It may work but it's not the best approach. I must be getting rusty because I'm not getting why that code doesn't error out because of it.

Here is my Final code that works fine. Thanks for your help.

Function ExportAppeals16()

Dim dbs As Database
Dim Path As String, Path1 As String

Path1 = "\ACNH_APPEALS.16.xlsx"
Set dbs = CurrentDb

Set rstable = dbs.OpenRecordset("tbl0070_FINAL_APPEALS_16")



Set excelApp = CreateObject("Excel.application", "")
excelApp.Visible = False


Set targetWorkbook = excelApp.Workbooks.Open(CurrentProject.Path & Path1)
targetWorkbook.Worksheets("Appeals.16").Range("A2" ).CopyFromRecordset rstable


If Dir(CurrentProject.Path & "\template" & Path1) <> "" Then
Kill (CurrentProject.Path & "\template" & Path1) 'Delete (strPath)
End If

targetWorkbook.SaveAs FileName:=CurrentProject.Path & "\template" & Path1

targetWorkbook.Close savechanges:=True


End Function

[Micron]

use code tags with proper indentation.

You're welcome.

[ssanfu]

You have several issues with your code.

I would be willing to bet that you do not have

Option Compare Database
Option Explicit

as the top two lines of EVERY code module.

You should have "Dim dbs As DAO.Database"
You have not declared variables "rstable, excelApp, targetWorkbook"
You did not do proper clean up

    rstable.Close
    Set rstable = Nothing

    Set targetWorkbook = Nothing
    Set excelApp = Nothing
    Set dbs = Nothing

I've modified your code - but it is UNTESTED!

Option Compare Database
Option Explicit

Function ExportAppeals16()

    Dim dbs As DAO.Database
    Dim rstable As DAO.Recordset
    Dim Path As String, Path1 As String
    Dim excelApp As Object
    Dim targetWorkbook As Object

    Path1 = "\ACNH_APPEALS.16.xlsx"
    Set dbs = CurrentDb

    Set rstable = dbs.OpenRecordset("tbl0070_FINAL_APPEALS_16")

    Set excelApp = CreateObject("Excel.application", "")
    excelApp.Visible = False


    Set targetWorkbook = excelApp.Workbooks.Open(CurrentProject.Path & Path1)
    targetWorkbook.Worksheets("Appeals.16").Range("A2").CopyFromRecordset rstable


    If Dir(CurrentProject.Path & "\template" & Path1) <> "" Then
        Kill (CurrentProject.Path & "\template" & Path1)    'Delete (strPath)
    End If

    targetWorkbook.SaveAs FileName:=CurrentProject.Path & "\template" & Path1

    targetWorkbook.Close savechanges:=True

    rstable.Close
    Set rstable = Nothing

    Set targetWorkbook = Nothing
    Set excelApp = Nothing
    Set dbs = Nothing
End Function

You might check out Write Data From a Recordset into an EXCEL Worksheet using EXCEL's CopyFromRecordset (VBA) by Ken Snell
Hid site is Ken's ACCESS Examples and Information for Working With EXCEL Workbook Files



And -- Please learn how to use code tags.......

[pnpez]

Thank you again!

You're right, I don't know how to use code tags. I will try to look on this site for examples.

[Welshgasman]

Thank you again!

You're right, I don't know how to use code tags. I will try to look on this site for examples.

You either use the # icon, or manually add code and /code, surround by square brackets [ & ]
I cannot put them together as the site will then rightfully interpret the characters as it should.

[pnpez]

Steve, your code worked BTW. I'm learning!!!!

[ssanfu]

Excellent!! ( that the code worked and you are learning)