Newbie: Error 1004

  • Thread starter Barry Aylett-Warner
  • Start date
B

Barry Aylett-Warner

I'm trying to read a set of array values into an excel worksheet. I can
transfer single values OK, but run into trouble when I try to do it in a
For..Next loop, getting a '1004' error.
eg
For x=0 to 23
ws.Cells(xlRow,xlCol) = Scores(y,x)
xlCol=xlCol+1
Next

Having tried to research the problem, I know that the problem lies with the
iterated call to Excel, but can't get the syntax correct.

Can anyone help please?

Thanks
Barry
 
B

Barry Aylett-Warner

Hi Frank, thanks for responding
Sorry, I was trying to keep it simple. Full procedure is :

Private Sub SavArr_Click()
Dim xlApp As Excel.Application
Dim wb As Workbook
Dim ws As Worksheet
Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Open("C:\Documents and Settings\My
Documents\Finals.xls")
Set ws = wb.Worksheets("Singles")
xlCol = 24
For y = 0 To 9
For x = 0 To 23
ws.Cells(ws.Cells(xlRow, xlCol)) = Scores(y, x)
xlCol = xlCol + 1
Next x
xCol = 24
xlRow = xlRow + 1
Next y
wb.SaveAs "C:\Documents and Settings\My Documents\Finals.xls"
wb.Close
xlApp.Quit
Set ws = Nothing
Set wb = Nothing
Set xlApp = Nothing
End Sub

The variable xlRow is defined in another procedure, having a value of 6.
Regards
Barry
 
T

Tom Ogilvy

numrw = Ubound(Scores,1)-Lbound(Scores,1)+1
numcol = Ubound(Scores,2)-Lbound(Scores,2)+1
Range("A1").Resize(numrw,numcol).Value = Scores

change A1 to the upper left corner where you want the data to start.
 
B

Barry Aylett-Warner

Tom,
thanks for the suggestion - am now getting '1004 - Method 'Range' of
Object'_Global' failed.
Any thoughts?

Regards
Barry
 
T

Tom Ogilvy

Sure. In the code you posted, you are working with another excel
application instance - it is unclear why or what application is running your
code, but you would have to incorporate my suggestion in your code so it is
properly qualified. Otherwise you might get '1004 - Method 'Range' of
Object'_Global' failed because your unqualified references are inconsistent
with what you are trying to do.

You didn't post your revision, so no specific suggestion can be offered.


--
Regards,
Tom Ogilvy




Barry Aylett-Warner said:
Tom,
thanks for the suggestion - am now getting '1004 - Method 'Range' of
Object'_Global' failed.
Any thoughts?

Regards
Barry
Click to expand...
 
B

Barry Aylett-Warner

Tom,

I created a test procedure as below and it returned the error mentioned.

Private Sub Command2_Click()
Dim xlApp As Excel.Application
Dim wb As Workbook
Dim ws As Worksheet
Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Open("C:\Documents and Settings\My
Documents\Finals.xls")
Set ws = wb.Worksheets("Singles")
xlCol = 24
xlRow = 6
numrw = UBound(Scores, 1) - LBound(Scores, 1) + 1
numcol = UBound(Scores, 2) - LBound(Scores, 2) + 1
Range(xlRow, xlCol ).Resize(numrw, numcol).Value = Scores
wb.SaveAs "C:\Documents and Settings\My Documents\Finals.xls"
wb.Close
xlApp.Quit
Set ws = Nothing
Set wb = Nothing
Set xlApp = Nothing
End Sub

Am trying to save a numeric array, Scores(9,23) to a block of cells
starting at X6.

Regards
Barry
 
T

Tom Ogilvy

Unless I missed something, this should fix it:
Qualified the Range and changed range to Cells
Shifted some stuff at the bottom
Avoided prompt to overwrite Finals.xls


Private Sub Command2_Click()
Dim xlApp As Excel.Application
Dim wb As Workbook
Dim ws As Worksheet
Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Open( _
"C:\Documents and Settings\My Documents\Finals.xls")
Set ws = wb.Worksheets("Singles")
xlCol = 24
xlRow = 6
numrw = UBound(Scores, 1) - LBound(Scores, 1) + 1
numcol = UBound(Scores, 2) - LBound(Scores, 2) + 1
ws.Cells(xlRow, xlCol ).Resize(numrw, numcol).Value = Scores
wb.close SaveChanges:=True
Set ws = Nothing
Set wb = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub

--
Regards,
Tom Ogilvy

Barry Aylett-Warner said:
Tom,

I created a test procedure as below and it returned the error mentioned.

Private Sub Command2_Click()
Dim xlApp As Excel.Application
Dim wb As Workbook
Dim ws As Worksheet
Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Open("C:\Documents and Settings\My
Documents\Finals.xls")
Set ws = wb.Worksheets("Singles")
xlCol = 24
xlRow = 6
numrw = UBound(Scores, 1) - LBound(Scores, 1) + 1
numcol = UBound(Scores, 2) - LBound(Scores, 2) + 1
Range(xlRow, xlCol ).Resize(numrw, numcol).Value = Scores
wb.SaveAs "C:\Documents and Settings\My Documents\Finals.xls"
wb.Close
xlApp.Quit
Set ws = Nothing
Set wb = Nothing
Set xlApp = Nothing
End Sub

Am trying to save a numeric array, Scores(9,23) to a block of cells
starting at X6.

Regards
Barry
Click to expand...
 
Top