Assistance with code to export/name file (1 Viewer)

[HeelNGville]

Hello, thanks in advance for any assistance. I have DB with a module that exports a table 'tbl_test' to a MS Excel File 'Load File,xlsx' and launches MS Excel, formats and saves the file based on a set of values. This works great (thanks to the unknown user that created) when the file is saved in the directory with the DB, however I need to write a second copy to a separate directory that the active DB is not stored. A copy of the destination file is located at T:\Planning\Load File.xlsx and this is where I need this code to write 'tbl_test' to. My limited coding skills has failed miserable, so any help in achieving above would be appreciated. Thank you!

Code below:

Public Function TferTbls()
' strTQName is the name of the table or query you want to send to Excel
' strSheetName is the name of the sheet you want to send it to


' strFilePath is the name and path of the file you want to send this data into.


Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim nfile As String


Dim strPath As String

Const xlCenter As Long = -4108
Const xlBottom As Long = -4107

'/commented for testing purposes
' On Error GoTo err_handler




'strPath = strFilePath

Set rst = CurrentDb.OpenRecordset("tbl_Test")
'/Is there anything to export?
If Not rst.EOF And Not rst.BOF Then
nfile = CurrentProject.Path & "\" & rst(0) & "-" & rst(1) & ".xlsx"
Else
MsgBox "No data in recordset", , "Export abandoned"
Exit Function

End If
'/ Does nfile already exist in the nominated location?
If Dir(nfile) <> "" Then
'delete the previous file
Kill nfile
End If



Set ApXL = CreateObject("Excel.Application")

Set xlWBk = ApXL.Workbooks.Open(CurrentProject.Path & "\Load File.xlsx")
'ApXL.Visible = True

Set xlWSh = xlWBk.Worksheets("Group")

xlWSh.Range("A1").Select

For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next

rst.MoveFirst
xlWSh.Range("A2").CopyFromRecordset rst
xlWSh.Range("1:1").Select
' This is included to show some of what you can do about formatting. You can comment out or delete
' any of this that you don't want to use in your own export.
With ApXL.Selection.Font
.Name = "Verdana"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
End With
ApXL.Selection.Font.Bold = True
With ApXL.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
' selects all of the cells
ApXL.ActiveSheet.Cells.Select
' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
' selects the first cell to unselect all cells

xlWBk.SaveAs nfile
xlWBk.Close

ApXL.Quit

Set ApXL = Nothing

rst.Close
Set rst = Nothing

Exit Function
err_handler:
DoCmd.SetWarnings True
MsgBox Err.Description, vbExclamation, Err.Number
Exit Function

End Function

 

[Cronk]

Why not just copy the first file to the second one?

FileCopy strSourceFile, strFileCopy

where the strings hold the full path and file name of the two respective files.

 

[HeelNGville]

There are multiple files within the directory, along with a 'templated' file that the code writes to & saves as xxxx-xxxx.xlsx.

So I may have file
ABC-123456.xlsx &
ABC-M23456.xlsm

and the 'saved' filenames are dynamic.

This is beyond my pay grade as I have adopted this from someone else...so any help, assistance, guidance......feel free. Thanks!

 

[Cronk]

Your original requirement was "I need to write a second copy to a separate directory.."

From your code, the Excel file being created is CurrentProject.Path & "\Load File.xlsx"

I suggested if you need a second copy in a separate directory, to copy it.

I don't see the relevance of the other files.

 

[HeelNGville]

Ok, let me backup as I may have done poor job of explaining.

Load File.xlsx is nothing more than template for the code to dump data from tbl_test, then renames the file based on the contents of row 1 & column 1 “-“ & column 3 of tbl_test, then saves the file and quits MS Excel application. So, I could process 50 transactions in a day, and the results could be:


AAAA-111111
BBBB-222222
XYTH-827123
DTGH-987520

The code works great for this purpose, however the need had surfaced to place a second copy (or new copy) of the same file in a separate directory in which the DB is not stored. The directory, along with destination template file is located at: T:\Planning\Load File.xlsx

So, the same steps would be performed as directed in the code, however in lieu of looking for the file in the same directory as the DB is stored, the path above would need to be accessed.

Hopefully I did a better job of clarifying. Thanks for your patience.

 

[Cronk]

In that case, why not insert after the line

nfile = CurrentProject.Path & "\" & rst(0) & "-" & rst(1) & ".xlsx"

the line

strDestFile = "T:\Planning\" & rst(1) & ".xlsx"

and after the created file is saved and closed

Filecopy nFile, strDestFile

 

[HeelNGville]

Many thanks! Had to modify slightly from:

strDestFile = "T:\Planning\" & rst(1) & ".xlsx"

to

strDestFile = "T\Planning\" & rst(0) & "-" & rst(1) & ".xlsx"

Worked like a charm. Thanks so much!