problem: run-time error 1004

[mar7632]

Hello everyone,



I have a problem related to exporting an Excel spreadsheet to an Access form.

I am using a module:

Option Compare Database

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

and a button which is located in my form whose function is to look up an excel file I previously saved and transfer all the excel data which is at the second sheet.

Private Sub cmdNavegar_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").Select
'Me.codigoRFQ = xlHoja.Range("A2").Value
xlHoja.Range("B2").Select
Me.solicitante = xlHoja.Range("B2").Value
.
.
.

xlHoja.Range("AJ2").Select
Me.comentarios = xlHoja.Range("AJ2").Value
 

xlLibro.Close
        
Set xlHoja = Nothing
Set xlLibro = Nothing
Set xlApp = Nothing

End Sub

There are some times that the export action works as intended. But quite often I receive an error called: "run-time error 1004"
When I check the error it shows me that the Range is wrong but it isn´t.
If i go back to my excel file and click on a different cell and then save it. Go then to my Access application I try again, usually it is solved.

I don´t why this happen. Is there any solution?

Thank you in advance.

[ssanfu]

I don't see anything in your code where you are exporting anything to Excel.

Maybe this link will help you with the code http://www.accessmvp.com/KDSnell/EXC...m#WriteRstFile


The main page for the Excel examples is Ken's ACCESS Examples and Information for Working With EXCEL Workbook Files

[mar7632]

Thank you for your response,

Is there some different approach to solve this problem?
I create a new Excel file, It seems to be ok, but then It corrupts. It is a little bit frustating.

[Micron]

Set xlApp = Excel.Application

Actually I don't care for your method of setting the excel object. You don't invoke Create or Create New but rather just seem to rely on it being open.
Suggest you take a look at http://www.accessmvp.com/KDSnell/EXC...m#WriteRstFile
and decide on whether or not you want to implement the Get test in that code or not. There's a ton of threads on how to use Automation within Access, and many of them point to Ken's site.

Not that the following affects your situation, you can simplify using any of these (don't think you need the () either):

vArchivo = buscaArchivo
If IsNull(vArchivo) Or vArchivo = "" Then Exit Sub
Dim xlApp As Excel.Application

Or
vArchivo = buscaArchivo
If Nz(vArchivo,"") = "" Then Exit Sub
Dim xlApp As Excel.Application

Or
If Nz(buscaArchivo,"") = "" Then Exit Sub
Dim xlApp As Excel.Application

[mar7632]

Hi everyone,

I think that I have finally solved the problem! hurra!

I eliminated this part of the code: xlHoja.Range("A2").Select

So, it will be like this:

Private Sub cmdNavegar_Click()
Dim vArchivo As String

vArchivo = buscaArchivo

If IsNull(vArchivo) Or vArchivo = "" Then
Exit Sub
Else
Me.Archivo.Value = vArchivo
 
End If
'B)----------------------------------------------------
 
 
Dim xlLibro As Excel.Workbook
Dim xlHoja As Excel.Worksheet
 

Set xlLibro = Workbooks.Open(vArchivo)
Set xlHoja = xlLibro.Sheets(2)
xlHoja.Visible = True
 

 

Me.solicitante = xlHoja.Range("B2").Value
Me.fecha_consulta = xlHoja.Range("C2").Value
Me.planta_gonvarri = xlHoja.Range("D2").Value
.
.
.
Me.comentarios = xlHoja.Range("AJ2").Value
 

xlLibro.Close
        
Set xlHoja = Nothing
Set xlLibro = Nothing
 
End Sub

Thank you.