merge to excel

  • Thread starter Thread starter Patty
  • Start date Start date
P

Patty

I have payroll detail records that need to be summarized on excel
spreadsheets. For example if I have 30 records representing 3 unique
companies, I need to create 3 excel control total sheets with totals
for each company merged into specific cells on the sheets. These
sheets are not empty, they have extensive formatting and data already
contained. The user then enters some additional data which is why I
can't use a report.

I have done this when merging to word but can't find info on excel.
TransferSpreadsheet doesn't seem to do what I need.

Thanks in advance...

p
 
Hi Patty,
if what you wanna do is to write something in a cell of an excel sheet
here's the code to do that:

Dim ExcelSheet As Object

Set ExcelSheet = CreateObject("Excel.Application")
ExcelSheet.Workbooks.Open "d:\tmp.xls" 'substitute tmp with the name of
the workbook
ExcelSheet.Application.Sheets("sheet2").Select 'substitute sheet2 with
the name of your sheet
With ExcelSheet.Application.Selection
.cells(2, 3).Value = 100 'substitute 2,3 with the address of the
cell where you wanna write and at the place of 100 put your value
End With
ExcelSheet.Application.DisplayAlerts = False
ExcelSheet.Save
ExcelSheet.Application.DisplayAlerts = True
ExcelSheet.Quit
Set ExcelSheet = Nothing

HTH Paolo
 
Thanks Paolo!

I haven't tested it yet but I think I can make it work.

Can I replace the 100 with some code that will extract data from an access
table? Can the where clause (or dsum) use another cell in the spreadsheet as
part of the selection criteria?

Thanks again!
P
 
Yes, 100 is just an example of what you can write in your cell but you can
replace it with whatever data you wanna write to your sheet.
And yes again, when you are "connected" to the sheet you can also read the
data in a cell and use it for some selection criteria.

e.g.
With ExcelSheet.Application.Selection
tmp = .cells(2, 3).Value
End With
where tmp is a variable defined in your vba code.

CU Paolo
 
Back
Top