Exporting many times to an access file gets overwritten

[mar7632]

Hi everyone,

I would like to export an access record (located in a form) into a excel sheet which is previously prepared in order to send it to a client. The code I am using is similar to the one I am using to import all the data from an excel file (https://www.accessforums.net/showthread.php?t=76013)

My idea is to send all the information which is in a form (in a certain record) to a excel model and being able to use the same excel model later for different records.

The data goes to the second excel sheet and then By relationship I move the data through the excel.

However, It gets overwritten the second time I use the excel file and gets corrupted.


Here is the code I am using:

Private Sub cmdMandarExcel_Click()
Dim vArchivo As String

vArchivo = buscaArchivo()

If IsNull(vArchivo) Or vArchivo = "" Then
Exit Sub
Else
Me.Archivo.Value = vArchivo
 
End If

Dim xlApp As Excel.Application
Dim xlLibro As Excel.Workbook
Dim xlHoja As Excel.Worksheet
Set xlApp = Excel.Application
Set xlLibro = xlApp.Workbooks.Open(vArchivo)
Set xlHoja = xlLibro.Sheets(2)

xlHoja.Range("A2").Value = Me.codigoRFQ
xlHoja.Range("B2").Value = Me.solicitante
.
.
.
.
xlHoja.Range("AV2").Value = Me.Archivo
 
xlLibro.Close
        
Set xlHoja = Nothing
Set xlLibro = Nothing
Set xlApp = Nothing

End Sub

and the module for vArchivo:

Public Function buscaArchivo() As String
Dim fDialog As Office.FileDialog
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
.AllowMultiSelect = False
.ButtonName = "Seleccionar"
.Title = "Seleccionar el archivo"
.InitialFileName = Application.CurrentProject.Path
.InitialView = msoFileDialogViewDetails
.Filters.Clear
.Filters.Add "All Files", "*.*"
If .Show = True Then
buscaArchivo = .SelectedItems(1)
Else
MsgBox "Ha pulsado el botón <Cancelar>."
End If
End With
End Function

As expected the code works properly, but the problem is related to the excel file. Any idea or suggestion?

Thank you in advance,

[June7]

Client needs an entire workbook for a single record? Or do you want to add more records to the same file later?

Save the modified workbook to a new filename and leave the template unchanged.

[mar7632]

Thank you June for your reply.

Yes, a client needs an entire workbook for a single record.

Where is the option to leave the template unchanged?

[June7]

Open the template, do edits, then save to a new filename.

xlLibro.SaveAs "filepath/filename"

[mar7632]

But, after sending the excel file to a certain client I need the excel model with the structure I prepared before.

Sorry for my ignorance, but where Do I write xlLibro.SaveAs "filepath/filename" ?

[June7]

Before xlLibro.Close

Need to substitute "filepath\filename" with your value. Maybe:

xlLibro.SaveAs CurrentProject.Path & "\temp.xlsx"

Then can delete the new file after sent to client.

The original file will be unchanged.

[mar7632]

Private Sub cmdMandarExcel_Click()
Dim vArchivo As String

vArchivo = buscaArchivo()

If IsNull(vArchivo) Or vArchivo = "" Then
Exit Sub
Else
Me.Archivo.Value = vArchivo
 
End If

Dim xlApp As Excel.Application
Dim xlLibro As Excel.Workbook
Dim xlHoja As Excel.Worksheet
Set xlApp = Excel.Application
Set xlLibro = xlApp.Workbooks.Open(vArchivo)
Set xlHoja = xlLibro.Sheets(2)

xlHoja.Range("A2").Value = Me.codigoRFQ
xlHoja.Range("B2").Value = Me.solicitante
.
.
.
.
xlHoja.Range("AV2").Value = Me.Archivo
 
xlLibro.SaveAs CurrentProject.Path & "\temp.xlsx"
xlLibro.Close
        
Set xlHoja = Nothing
Set xlLibro = Nothing
Set xlApp = Nothing

End Sub

Something like this?

[June7]

Yes, like that.