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:
Code:
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:
Code:
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,