Manipulating Excel from Access

[CDM]

I run a report in Excel format with two header rows. After deleting the first
row, I rename the headings of the 2nd row: "Field1", "Field2",..."Field39". I
save the workbook and then open Access and import the file using the
TransferSpreadsheet function. Is there a way to do all the pre-import row
manipulation from within Access? I've tried cutting and pasting Excel's
macro, but it doesn't seem to work. Thanks for any help you might be able to
offer.

 

[golfinray]

No. Microsoft lost a lawsuit and so you can no longer manipulate Excel data
through Access. You can have a command button to open your Excel file in
access and edit it there.

 

[JP]

Sure, just instantiate Excel and then you can do everything
programmatically. i.e.:

Dim xl As Object ' Excel.Application
Dim xlsht As Object ' Excel.Worksheet
Set xl = Excel.Application
Set xlsht = xl.Sheets(1)

' code to manipulate Excel worksheet

Then close the workbook and execute the DoCmd.TransferSpreadsheet
Method.

--JP

 

[Angela]

Hi,

Can you help with below code??

Option Compare Database
Public Sub TransferReport()


Dim varFileName As String
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim MyRange As String


varFileName = "D:\MyFile.xls"
'EXPORT DATA
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MONTH
END REPORT", varFileName, False, "Sheet1"


Set xlWb = ActiveWorkbook
xlWb.Sheets.Add
Set xlWs = xlWb.Sheets("Sheet2")


xlWs.Cells(2, 1).CopyFromRecordset rsXcl


xlWs.Range("A1").Addresslastcell = xlW.Range("A1").SpecialCells
(xlCellTypeLastCell).Address
MyRange = Range("$A$1:" & lastcell)


' Add pivot table
With xlWb
.PivotCaches.Add SourceType:=xlDatabase, _
SourceData:=MyRange
.CreatePivotTable TableDestination:="", _
tablename:="Pivottable1"
End With


End Sub

 

[JP]

What specifically do you need help with?

 

[Angela]

Hey JP,

See the export works till below line.

varFileName = "D:\MyFile.xls"
'EXPORT DATA
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MONTH
END REPORT", varFileName, False, "Sheet1"

After this I want to make a pivot table of this data in sheet2 which
is not working.
I'm stuck with creating sheet2 in same workbook & creating the pivot
table in it.

By using this routine, I make a new exce file each time with data in
sheet1.


Presently, I have a work around with it.
I have a excel workbook.
I export the data from my query to that file.
Once exported, I open the excel workbook and run a macro in that excel
workbook to make a pivot table in sheet2.

As mentioned earlier, I want to have either of the following but would
love to have option one.

Option 01:
Export to a new excel file and create pivot table of the data in
sheet2. No macro in that excel file since all work will be done from
macro in access.

Option 02:
This I have already done. Export to a excel file already specified and
run a pivot table macro within that excel workbook.

 

[JP]

I'm confused. First you write that your code works until the line that
exports to Excel. Then you write that you're stuck on creating a
second sheet and putting a pivot table on it. So what are you having a
problem with, the exporting or the sheet+pivottable creation?

--JP

 

[Angela]

Hey JP,

I'm stuck with the following:

- First creating a sheet2 in same workbook in which I exported the data in
sheet1.
- Creating a pivot table in sheet2 based on data in sheet1 with VBA code.



I'm confused. First you write that your code works until the line that
exports to Excel. Then you write that you're stuck on creating a
second sheet and putting a pivot table on it. So what are you having a
problem with, the exporting or the sheet+pivottable creation?

--JP

 

[JP]

OK. First, you'll need to change the way you instantiate Excel.

"Set xlWb = ActiveWorkbook" won't work, because Access doesn't have an
ActiveWorkbook Object. You probably want something like this:

' start new instance of Excel and open the workbook you created
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open varFileName

Then you can do "xlWb.Sheets.Add" as you have in your code, but you
want to set an object reference to it so you don't lose it, because
the next line of code ("Set xlWs = xlWb.Sheets("Sheet2") ") isn't
guaranteed to always point to the new sheet. Something like

' add worksheet to end
xlWb.Sheets.Add(After:=xlWb.Sheets(xlWb.Sheets.Count))
' set object reference
Set xlWs = xlWb.Sheets(xlWb.Sheets.Count)
' name the sheet
xlWs.Name = "Sheet2"

You might want to give the new sheet a more productive name than
"Sheet2".

To create the PT on sheet 2, first you need to create a PivotCache.
See http://msdn.microsoft.com/en-us/library/aa221688(office.11).aspx
and look for the "Add method as it applies to the PivotCaches object."
section. Remember you are in Access (not Excel) so you need to fully
qualify all Excel references. i.e.

Dim PC As Object
Set PC = xlWb.PivotCaches.Add

not

Set PC = PivotCaches.Add

Then you call the PivotTables.Add Method and pass the PivotCache
object to it. i.e.

Dim PT As Object
Set PT = xlWb.PivotTables.Add(PC)

Of course there are other arguments to both methods that you must
supply at runtime.

--JP

 

[Angela]

Hey JP,

I will give this a try since what ever you wrote is already that I had
in mind : ) but couldn't put into coding.
...Like to initiate excel in access and then rest comes on easily.

Thank you so much. Been great help.

Well I also managed to do something else.

I have exported the data to a excel file. I have placed the pivot
macro in excel file. I'm calling that excel macro from access using a
function in access.

Thanks once again for the input.

Angela.