redman08
Last Activity: Dec 3 '09, 12:04 PM
Joined: Dec 30 '08
Location:
-
I've taken the connection details from what was recorded and tried to apply them to my code. I'm new to "connection s" so there may be a problem, there. Having set up the connection details, I've tried to run the code with the results as per my previous post. -
I have tried various combinations in trying to establish a connection (obviously not my stringest suit) and then using it to create the pivot.
The current version looks like this:
...Code:Dim Excelconn As ADODB.Connection Set Excelconn = New ADODB.Connection With Excelconn .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Y:\PROVIDER SECONDARY CARE\PTL MONITORING\18 Weeks Outcome Reporting\Databases\18Wk
Leave a comment:
-
Only the last line was missing. Here it is again in full:
...Code:Workbooks("Book1").Connections.Add "18Wk RTT Reporting Data", "", Array( _ "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=Y:\PROVIDER SECONDARY CARE\PTL MONITORING\18 Weeks Outco" _ , _ "me Reporting\Databases\18Wk RTT Reporting Data.mdb;Mode=Share DenyLeave a comment:
-
Good morning!
Here's what was recorded:
...Code:Range("A7").Select Workbooks("Book2").Connections.Add "18Wk RTT Reporting Data", "", Array( _ "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=Y:\PROVIDER SECONDARY CARE\PTL MONITORING\18 Weeks Outco" _ , _ "me Reporting\Databases\18WkLeave a comment:
-
Grabbing the code is easy enough. Making it work....that's the problem. There's lots of 'dummy' or null entries, double/triple sets of double-inverted commas, etc. I'll paste in the code tomorrow for you to see. Meanwhile, the search (for inspiration) goes on.Leave a comment:
-
Thanks, I did create one, as you suggested with 'macro recording on', and then transposed the code to my Access module. I tred to filter out various bits of code in an attempt to make it run, but to no avail. I'll repeat the exercise and check properties, etc. in the morning. I have successfully created code for situations where I have built the data in one sheet, then created the pivot in another, but, as I said originally, because of the large...Leave a comment:
-
I did record a 'pivot create' in Excel and transferred it, but when I ran the VBA code it didn't like it.
Re-reading what I orginally wrote, and just to clarify:
I'm writing VBA in Access, to run a module which will create the pivot (in Excel) using a table in the same database that the module will reside.
I can easily create the data in Excel and then pivot it, but I don't want to store all the data in another...Leave a comment:
-
Create Excel Pivot in VBA with Access Table as source
I am writing some VBA to create an Excel pivot table in a new worksheet using an existing local table.
Because of volumes, I don't want to store the original data (on another sheet and hide it) within the workbook.
Any ideas for the "SourceType :=" and "SourceData :=" parameters, or an example of a working model?
Thanks. -
I've uploaded a .txt file, becauseof size restrictions here. I've also had to remove a lot of the spacing in between the entries. The entries represent one pageful which is produced - I only wanted the coding bits, i.e. commands, operands, parameters, etc.
I've found coding previously which allowed access to queries (QueryDef/qdf, etc.) so I'm hoping to find something similar for macros....Leave a comment:
-
Eureka!
It is Create, not Add, and the remaining parameters are as they are now.
Has run...now only the remaining bits...formatti ng...usage., etc.
Many thanks to all contributors.Leave a comment:
-
I've added Microsoft Excel 12.0 Object Library and Microsoft Office 12.0 Object Library into my References....a nd am still getting '448' errors on line 2.
The code currently looks like this:
...Code:With AppExcel.ActiveWorkbook.PivotCaches With .Add(SourceType:=xlDatabase, SourceData:=Datasheet.Range("A1:H" & LastRowNumber), Version:=3) Set PTable = .CreatePivotTable(TableDestination:=CurrentSheet.Range("A6"),Leave a comment:
-
Checked on Excel: xlPivotTableVer sion12 is set to 3, so have replaced xlPivotTableVer sion12 with a constant.
Still no go!
I don't have "Microsoft Excel xx.x Object Library" referenced.Leave a comment:
-
Re. xlPivotTableVer sion12 ...I've got a blank line.Leave a comment:
-
Removing 'Version' parameter only produces a '448' error.Leave a comment:
-
Fishval, "LastRowNum ber is set to 18 when the error occurs.
When using ".Add", I get an error '448' - Named argument not found,;
When using ".Create", i get an error '5' - Invalid procedure call or agument.
How do I check if "xlPivotTableVe rsion12" is set up ok (apologies if too daft a question)Leave a comment:
-
Just to put more meat on the bones regarding definitions (and to repeat the problem coding):
...Code:Dim CurrentSheet As Variant Dim Datasheet As Variant Dim LastRowNumber As Integer Dim AppExcel As Object Set AppExcel = CreateObject("excel.application") LastRowNumber = AppExcel.WorksheetFunction.CountA(CurrentSheet.Range("A1:A65536")) Dim PTable As VariantLeave a comment:
-
The error is on Line #2Leave a comment:
-
The current code looks like this, where Datasheet is the worksheet containing the data, and CurrentSheet is a new sheet being created to hold the Pivot Table:
...Code:With AppExcel.ActiveWorkbook.PivotCaches With .Add(SourceType:=xlDatabase, SourceData:=Datasheet.Range("A1:H" & LastRowNumber), Version:=xlPivotTableVersion12) Set PTable = .CreatePivotTable(TableDestination:=CurrentSheet.Range("A6"),Leave a comment:
-
This is not homework, nor a project question.
I have come across various bits of code on the net and looked at output from a macro recording when creating a pivot table in escel (2007).
However, when transferring this to a vba module, I keep getting errors. In a vain attempt to see if there was a quick and simple resolution to this problem, I raised this post.
I have seen far simpler (I know, in my opinion)...Leave a comment:
-
Thanks, but when using that option, it produces a listing with approx. 4 entries per page, and not very readable at that.Leave a comment:
No activity results to display
Leave a comment: