Problem VBA code to save in Excel database

[Joke]

I attaching here major part of my code to save the data of a range called guardar2 in one workbook into an second excel workbook which functions as the database.

While it was working fine upto now after I increased the guardar-range and increased it doesnot work any more giving error 1004 after inserting the new row in the database that is on line .activesheet..... = activesheet.....

I'm wasting my whole day on this problem and would be great of anybody has an idea what could be the problem? I even tried reducing Guardar2 to one data and saving it into A2 in the database also doesnot work.

Thanks!

Joke


Sub Button_guardar()
'
'in the original workshoot copy data to save into range guardar2
Sheet6.Range("guardar2").Value = Sheet6.Range("guardar1").Value

'location of the database
strFname = Range("ubicacion").Value
Set ActiveSh = ActiveSheet()

'open the databasefile
Set OpenedWb = Workbooks.Open(Filename:=strFname,WriteResPassword:=Sheet9.Range("password").Value)

'In the database enter a new row and save teh data of guardar 2
With OpenedWb
.ActiveSheet.Range("A2").EntireRow.Insert
.ActiveSheet.Range("a2:z2").Value = ActiveSh.Range("guardar2").Value
OpenedWb.Close (True)
End With

End Sub

 

[bat17]

Is "guardar2" the same size range as A2:Z2. if not this would cause an error.

peter

 

[Joke]

I have checked that but they are both exactly 26 cells. And even if I do it with 1 cell adn A2 the code doesnot work anymore..........

Any other ideas?

Maybe it would be better to use ADO to save teh data into the database. But unfortunately I only know the ADO code to get data not to save data into databases. I'm also not sure whether a ADO-save code would work for Excel97 as my application will be used by Excel 97/00/xp.

So might be easier to stick to this code only that somewhere there seems to be an error.

Any other ideas?

Joke

 

[bat17]

I tried you code just Dim'ming the variables and setting the named ranges to ordinary ranges so that it would run on my machine.

Like this it runs with out error. It may help you to pin down the error you have.

Sub Button_guardar()
Dim strFname As String
Dim ActiveSh As Worksheet
Dim OpenedWb As Workbook
'
'in the original workshoot copy data to save into range guardar2
Sheet2.Range("a2").Value = Sheet2.Range("a1").Value

'location of the database
strFname = Range("a3").Value
Set ActiveSh = ActiveSheet()

'open the databasefile
Set OpenedWb = Workbooks.Open(Filename:="C:\myfile.xls")

'In the database enter a new row and save teh data of guardar 2
With OpenedWb
    .ActiveSheet.Range("A2").EntireRow.Insert
    .ActiveSheet.Range("a2:z2").Value = ActiveSh.Range("a2:z2").Value
    OpenedWb.Close (True)
End With

End Sub

HTH

Peter

 

[Joke]

Peter,

Thanks even without the dimming I got it working because I took out the following sentence.

OpenedWb.Sheets("aprobados").Select

But you are probably right that it is better with dimming

My problem is that I have one case where I want to saving in sheet1 and another case where I need to save in sheet2 of the database. But if I add any sentence to activate one of the two sheets the code doesnot work anymore and give the error as indicated earlier.

Is there a way to open the workbook on sheet2 instead of on sheet1? that would solve my problem!

Thanks

Joke