Update Excel Spreadsheet from Access Report

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

Hi,
I am tking values from a report and putting them into a spreadsheet, and I
am slowly but surely getting there, but have a problem with the below code,
It updates the spreadsheet Linktest.xls, but when I run another report and
try again, it places the values in the wrong cells.

If I first open the spreadsheet and save it with cell 1,1 selected, it then
works fine. Can someone please point out my obvious, (although not to me),
error.

Private Sub Report_Close()

On Error GoTo Report_Close_Click_Err
If MsgBox("Do you want to update spreadsheet", vbYesNo, "Computer
Says......") = vbYes Then


Dim Trec
Dim Tenav
Dim Green
Dim Yellow
Dim Pink
Dim Red
Dim Scheme As String

Trec = [Text14]
Tenav = [Text36]
Green = [Text24]
Yellow = [Text26]
Pink = [Text28]
Red = [Text30]
Scheme = [Text43]


'Excel Code

Dim ExcelSheet As Object

Set ExcelSheet = CreateObject("Excel.Application")
ExcelSheet.Workbooks.Open "C:\Linktest.xls" 'substitute tmp with the
name of the workbook
ExcelSheet.Application.Sheets("sheet1").Select 'substitute sheet2 with
the name of your sheet
With ExcelSheet.Application.Selection
.cells(1, 1).Value = Scheme 'substitute 2,3 with the address of the
cell where you wanna write and at the place of 100 put your value
.cells(91, 9).Value = Trec
.cells(81, 9).Value = Green
.cells(82, 9).Value = Yellow
.cells(83, 9).Value = Pink
.cells(84, 9).Value = Red
.cells(87, 12).Value = Tenav
.cells(1, 1).Value = Scheme

End With
ExcelSheet.Application.DisplayAlerts = False
ExcelSheet.Save

ExcelSheet.Application.DisplayAlerts = True
ExcelSheet.Quit
Set ExcelSheet = Nothing


'End Excel Code

End If

DoCmd.Restore


Command45_Click_Exit:
DoCmd.Restore
Exit Sub

Report_Close_Click_Err:
MsgBox Error$
ExcelSheet.Application.DisplayAlerts = True
ExcelSheet.Quit
Set ExcelSheet = Nothing
Exit Sub



End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
End Sub

Thank you in anticipation.

Kevin
 
Try replacing

ExcelSheet.Application.Sheets("sheet1").Select
With ExcelSheet.Application.Selection
.cells(1, 1).Value = Scheme
.cells(91, 9).Value = Trec
.cells(81, 9).Value = Green
.cells(82, 9).Value = Yellow
.cells(83, 9).Value = Pink
.cells(84, 9).Value = Red
.cells(87, 12).Value = Tenav
.cells(1, 1).Value = Scheme
End With

With ExcelSheet.Application.Sheets("sheet1")
.cells(1, 1).Value = Scheme
.cells(91, 9).Value = Trec
.cells(81, 9).Value = Green
.cells(82, 9).Value = Yellow
.cells(83, 9).Value = Pink
.cells(84, 9).Value = Red
.cells(87, 12).Value = Tenav
.cells(1, 1).Value = Scheme
End With

In general, it's not a good idea to use the Select method, or rely on the
Selection object when using Automation.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Kevin said:
Hi,
I am tking values from a report and putting them into a spreadsheet, and I
am slowly but surely getting there, but have a problem with the below
code,
It updates the spreadsheet Linktest.xls, but when I run another report and
try again, it places the values in the wrong cells.

If I first open the spreadsheet and save it with cell 1,1 selected, it
then
works fine. Can someone please point out my obvious, (although not to me),
error.

Private Sub Report_Close()

On Error GoTo Report_Close_Click_Err
If MsgBox("Do you want to update spreadsheet", vbYesNo, "Computer
Says......") = vbYes Then


Dim Trec
Dim Tenav
Dim Green
Dim Yellow
Dim Pink
Dim Red
Dim Scheme As String

Trec = [Text14]
Tenav = [Text36]
Green = [Text24]
Yellow = [Text26]
Pink = [Text28]
Red = [Text30]
Scheme = [Text43]


'Excel Code

Dim ExcelSheet As Object

Set ExcelSheet = CreateObject("Excel.Application")
ExcelSheet.Workbooks.Open "C:\Linktest.xls" 'substitute tmp with the
name of the workbook
ExcelSheet.Application.Sheets("sheet1").Select 'substitute sheet2 with
the name of your sheet
With ExcelSheet.Application.Selection
.cells(1, 1).Value = Scheme 'substitute 2,3 with the address of the
cell where you wanna write and at the place of 100 put your value
.cells(91, 9).Value = Trec
.cells(81, 9).Value = Green
.cells(82, 9).Value = Yellow
.cells(83, 9).Value = Pink
.cells(84, 9).Value = Red
.cells(87, 12).Value = Tenav
.cells(1, 1).Value = Scheme

End With
ExcelSheet.Application.DisplayAlerts = False
ExcelSheet.Save

ExcelSheet.Application.DisplayAlerts = True
ExcelSheet.Quit
Set ExcelSheet = Nothing


'End Excel Code

End If

DoCmd.Restore


Command45_Click_Exit:
DoCmd.Restore
Exit Sub

Report_Close_Click_Err:
MsgBox Error$
ExcelSheet.Application.DisplayAlerts = True
ExcelSheet.Quit
Set ExcelSheet = Nothing
Exit Sub



End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
End Sub

Thank you in anticipation.

Kevin
Click to expand...
 
Thanks Doug, I will give this a try, and let you know the result.

I dont suppose you can help me with the Excel Save as command. I want to
'Save As' the spreadsheet to the value of the variable "Scheme".


I tried
ExcelSheet.SaveAs Filename:=Scheme

But it didnt work

Thanks again
Kevin


Douglas J. Steele said:
Try replacing

ExcelSheet.Application.Sheets("sheet1").Select
With ExcelSheet.Application.Selection
.cells(1, 1).Value = Scheme
.cells(91, 9).Value = Trec
.cells(81, 9).Value = Green
.cells(82, 9).Value = Yellow
.cells(83, 9).Value = Pink
.cells(84, 9).Value = Red
.cells(87, 12).Value = Tenav
.cells(1, 1).Value = Scheme
End With

With ExcelSheet.Application.Sheets("sheet1")
.cells(1, 1).Value = Scheme
.cells(91, 9).Value = Trec
.cells(81, 9).Value = Green
.cells(82, 9).Value = Yellow
.cells(83, 9).Value = Pink
.cells(84, 9).Value = Red
.cells(87, 12).Value = Tenav
.cells(1, 1).Value = Scheme
End With

In general, it's not a good idea to use the Select method, or rely on the
Selection object when using Automation.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Kevin said:
Hi,
I am tking values from a report and putting them into a spreadsheet, and I
am slowly but surely getting there, but have a problem with the below
code,
It updates the spreadsheet Linktest.xls, but when I run another report and
try again, it places the values in the wrong cells.

If I first open the spreadsheet and save it with cell 1,1 selected, it
then
works fine. Can someone please point out my obvious, (although not to me),
error.

Private Sub Report_Close()

On Error GoTo Report_Close_Click_Err
If MsgBox("Do you want to update spreadsheet", vbYesNo, "Computer
Says......") = vbYes Then


Dim Trec
Dim Tenav
Dim Green
Dim Yellow
Dim Pink
Dim Red
Dim Scheme As String

Trec = [Text14]
Tenav = [Text36]
Green = [Text24]
Yellow = [Text26]
Pink = [Text28]
Red = [Text30]
Scheme = [Text43]


'Excel Code

Dim ExcelSheet As Object

Set ExcelSheet = CreateObject("Excel.Application")
ExcelSheet.Workbooks.Open "C:\Linktest.xls" 'substitute tmp with the
name of the workbook
ExcelSheet.Application.Sheets("sheet1").Select 'substitute sheet2 with
the name of your sheet
With ExcelSheet.Application.Selection
.cells(1, 1).Value = Scheme 'substitute 2,3 with the address of the
cell where you wanna write and at the place of 100 put your value
.cells(91, 9).Value = Trec
.cells(81, 9).Value = Green
.cells(82, 9).Value = Yellow
.cells(83, 9).Value = Pink
.cells(84, 9).Value = Red
.cells(87, 12).Value = Tenav
.cells(1, 1).Value = Scheme

End With
ExcelSheet.Application.DisplayAlerts = False
ExcelSheet.Save

ExcelSheet.Application.DisplayAlerts = True
ExcelSheet.Quit
Set ExcelSheet = Nothing


'End Excel Code

End If

DoCmd.Restore


Command45_Click_Exit:
DoCmd.Restore
Exit Sub

Report_Close_Click_Err:
MsgBox Error$
ExcelSheet.Application.DisplayAlerts = True
ExcelSheet.Quit
Set ExcelSheet = Nothing
Exit Sub



End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
End Sub

Thank you in anticipation.

Kevin
Click to expand...
Click to expand...
 
ExcelSheet refers to the Excel application itself. SaveAs is a method of the
Workbook object.

Since you've only got a single workbook, try:

ExcelSheet.Workbooks(1).SaveAs FileName:=Scheme

You should then close it as well:

ExcelSheet.Workbooks(1).Close
 
Back
Top