VBA: Create PivotTable From A DAO Recordset
I have been creating pivot tables (from the active workbook) with ADO for a while and its working great.
I just tried to switch to DAO and adapted my code accordingly but the new code fail at:
Code: ....
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
PivotTable Code: The PivotTable Field Name Is Not Valid
The code I'm using that results in the following error message: "Run-Time Error '1004': The pivotTable field name is not valid. To create a pivottable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a pivottable field, you must type a new name for the field." Here's the code, I've seperated it into each sub hoping that will make it easier to read: Option Explicit Sub main_prog() Call td_metrics_import Call pt_td_metrics("Pivot_Page1", "PivotTable1", "PivotTable2") Call pt_td_metrics("Pivot_Page2", "PivotTable3", "PivotTable4") Call create_graph End Sub...............
View Replies!
View Related
Error Extracting Pivottable Data With VBA
I try to extract datas from a pivottable (so one data source) which could change. The items of one field are changing (given by the user through Inputbox): "batch" The items of the second field ("type plate") are at each time visible (the macro make them visible to have the complete information concerning the "batch") So I want to extract values and labels from one particular pivottable (the one I display), selecting several batches. But the problem is that some of the items have no value, so disapear fron the table) even if they are visible. As I extract the value on each item (see code below) I get an error message saying "runtime error 1004 application-defined" when the macro read "valeur". Dim pt1 As PivotTable Dim pf As PivotField Dim pi As PivotItem Dim pj As PivotItem Dim i As Double Dim valeur As String I tried to go through this problem using "on error goto solution" but it works only one time, and after same error message How coud I avoid to calculate "valeur" with the Items (pi) not displayed ?
View Replies!
View Related
SQL With DAO
I've discovered excel can do SQL !!! It feels like I'm the primitive caveman discovering fire Well lets not talk about how much time I've wasted writing code that would have been soooooooo much easier in SQL .... Instead, lets talk about BEST PRACTICES using SQL in EXCEL I would like to make this thread a good reference for others ( not much online surprisingly ). So first and foremost: #1 - Is DAO the right choice ?
View Replies!
View Related
Open The Recordset
i have a select statement, that pulls one row from access and i need to get the data and assign it to different fields in a userform. however, because it only pulls one row, its at .eof and closes, so any following statements cause errors because the recordset is closed. qc.Open "SELECT * FROM [Equipment_Table] WHERE [Equipment_QC] =" & combobox12.text, cn, adOpenStatic Equipment_Title.text = qc.fields.item("Equipment_Title") Equip_File = qc.fields.item("Equipment_File") Equip_ID = qc.fields.item("Equipment_ID")
View Replies!
View Related
PasteSpecial Format CSV From Recordset
Currently, my code involves making a connection to a db then run queries. Results from the queries goes to recordset & from the recordset copy to specified range in excel worksheet. I am trying to do an automation process. The problem is that once the record is pasted in excel worksheet, the date column is not being recognised as date therefore excel function (vlookup) is not giving me the results in my report. When i do a manual PasteSpecail as CSV into the worksheet from the query result, my report gets populated with data which is correct. i've tried doing a pastespecial format:="CSV" but it doesnt work. Is there any codes that i can use to copy from the recordset as a CSV format pastespecial?? Or any other ways that can be done?
View Replies!
View Related
Current Recordset Converts In An Array
I have a sub-routine I call on that takes a current recordset and converts it to an Array. I would like to make this sub-routine tool more flexable by being able to specify THE NAMES for the recordset and an array involved. For example Sub My_Code() ....blal bla bla.... current code... Call Recordset_To_Array( Recordset_name, Array_name ) ....bla bla bla... continued end sub And the subroutine tool: Sub Recordset_To_Array( Recordset_name as String, Array_name as string ) 'Code that generates RS 'Need to take Recordset_name string value and use it in the line below at "XXXXX" Dim XXXXX as Recordset 'Code that generates array 'Need to take Array_name string value and use it in the line below at "XXXXX" Dim XXXXX as Variant End Sub
View Replies!
View Related
Insert Ado Recordset Into Table
I'm getting extremely frustrated with trying to find some code that allows me to paste an ADO recordset into a database table! What I am doing is on a weekly basis is creating a plan of which containers my company needs to devan. We have a system that stores shipping information about these containers and I get shipping information delivered to me via e-mail from one of our major shipping companies. Their data is more accurate so what I do is paste the data I get from them into my container planning sheet and import it into out Teradata Warehouse to query against using ADO and I import this one record at a time. This is extremely slow! In order to get around this I want to create a recordset from this data and then paste the recordset into a table in the database. So far this is my ....
View Replies!
View Related
Working With Recordset In A Range Of Cells
how i can display different values using the Selection.FormulaArray, ok look at my Dim mySheet As Worksheet ' On Error GoTo errors Dim x As Integer x = TextBox1.Text Dim y As Integer y = RefEdit2.value Set mySheet = Excel.ActiveSheet mySheet. Range(RefEdit1).Select Selection.FormulaArray = "=multiplie_value(""" & x & """, """ & y & """)" MsgBox Selection.FormulaArray Now the function i call: Public Function multiplie_value(valor As Integer, multiplo As Integer) As Integer multiplie_value = Val(valor) * multiplo End Function thats an example. so the real question is i create a form, the user enter some values to search to a database, the user select an area(a range) where they want the data to be display, but i dont how to display that info that is coming from the database in the range that the user selected. The info displayed in the range should look like a tabular report, column headers, etc.
View Replies!
View Related
DAO Query On Password Protected File: Could Not Decrypt File
I have an existing application that uses DAO sql queries to populate a form. It has always worked fine. Earlier this week, I password protected the spreadsheet. Now my DAO query fails. I get a run-time error '3161'. Could not decrypt file. ' Const stExtens As String = "Excel 8.0;HDR=Yes;" Const stExtens As String = "Excel 8.0;HDR=Yes;PWD=money16;" Private Sub Execute_Query(SQL) 'Instantiate the DAO objects. strDb = ThisWorkbook.FullName Set DAO_ws = DBEngine.Workspaces(0) ' code fails on the line below Set DAO_db = DAO_ws.OpenDatabase(strDb, False, True, stExtens) Set DAO_rs = DAO_db.OpenRecordset(SQL, dbOpenForwardOnly) End Sub
View Replies!
View Related
Problem Displaying Single Record In Userform Listbox From Recordset
I am trying to populate a 3 column listbox in a userform from SQL Server via ADO. When the result set consists of more than one record, there is no problem and the data is displayed properly (ie each piece of data is in its appropriate column) eg... StockCode..........QtyReqd.........JobDeliveryDate test1...................1................. 01/01/1900 test2...................1..................31/12/1900 test3...................3..................18/02/1900 however when the recordset returns only a single record, the data does not transpose and views as below (ie each piece of data in the record is on a different line in the first column StockCode...........QtyReqd.........JobDeliveryDate test1 1 01/01/1900
View Replies!
View Related
Using VBA To Create VBA Code On The Fly
I have a vba module that reads a value from a txt file and saves that value in a variable called "iniInfo". I want to permanently assign that value programatically by using VBA code to create a Public variable e.g. "PUBLIC CONST conFIg = iniInfo" in my modGlobalVar Module. (Of course, this would only create the code if the "conFig" variable doesn't already exist.)
View Replies!
View Related
Create Graph Using VBA
I want to create excel graph using VBA and found difficulties on chart name set to default. the script is like this : ActiveSheet.ChartObjects("Chart 31").Activate Can I set my own name of the chart instead of "Chart 31" (excel default) ?
View Replies!
View Related
VBA To Create New Sheet ...
I would like to be able to have a macro, linked to a button, so that when you press that button it creates a new named sheet based on data in a particular cell. I.e. There is a table with the following data: Apples (A1) Oranges (A2) Pears (A3) There are also the sheets Apples, Oranges & Pears. You enter 'Grape' in cell A4, then when you press the button it creates a new sheet named Grape. Also when a new sheet is created, the 'input cell', in this case A4, is moved down one so that when the next sheet is needed to be added, it doesn't just add the same sheet (i.e. in the example above, we don't end up with a new sheet named 'Grape' each time we click the button).
View Replies!
View Related
Create Tag In VBA?
I found this really cool code that creates a tag cloud in Excel using VBA. It's far too complex for my understanding but I was hoping I could get some of the experts opinions on here about modification possibilities. I have attached the macro-enabled 2007 workbook. What I am trying to do is search through a list of keywords and determine each keyword's density within a list. The list for example could look like this: slow windows xp windows xp running slow windows xp computer [slow windows xp] [windows xp running slow] [windows xp computer] [computer running slow windows xp] "computer running slow windows xp" "slow windows xp" "windows xp running slow" "windows xp computer" You'll notice the characters " [ ] of which I would like to ignore when the cloud builds. The cloud would list the most dense keywords first, and gradually decrease to the least dense keywords. The macro code is inside the workbook but I'll list it here to:
View Replies!
View Related
Create VBA InputBox
I am trying to create an inputbox using code from this site: Sub NumericDataType() Dim lNum As Long On Error Resume Next Application.DisplayAlerts = False lNum = Application.InputBox _ (Prompt:="Please enter you age.", _ Title:="HOW OLD ARE YOU", Type:=1) On Error Goto 0 Application.DisplayAlerts = True If lNum = 0 Then Exit Sub Else MsgBox "You are " & lNum & " years old." End If End Sub I get an error on the first line that says, "Compile error: argument not optional".
View Replies!
View Related
Name New Sheet Used For PivotTable
In the macro I am writing I open a pivot table which as you know creates a sheet. I would like to name that sheet. My problem is that the default name(number if you will) changes every time I run my macro so I am unable to define which sheet I want to give a name.
View Replies!
View Related
Create Comparison Code In VBA
I have to do monthly comparisons of two excel worksheets, one being a vessel timesheet and the other being an excel download from manpower software that we use. The comparison that is being made are days of the month, whilst the unique identifier between the two sheets (To make it easier to understand I will call them sheet1 and sheet2) is an employee ID. Both sheets, alongside the name and employee ID, list all the days of the month that employees have worked. If they have worked, there is a "1" under that day, if they have not the cell is blank. What I currently do manually is: 1: Search for the employee ID (and/or name) in sheet1 and compare the days with the employee ID in sheet2. 2: Highlight the differences 3: Move onto the next employee ID All differences are highlighted in sheet2. Differences highlighted are in the days only, where if any days are missing/extra in either sheet, then the particular cell(s) are highlighted in yellow. If an employee is missing from sheet1 but is in sheet2, then all the days are highlighted (in sheet2). If an employee is missing from sheet2 but is in sheet1, then that particular employee is copied to the bottom of sheet2 (after last row). As this is horribly complicated to explain, I can attach an example if this is sounding rather inexplicable? Is it possible to attach a xls to a thread?
View Replies!
View Related
Create A New Workbook By Vba Of Excel
I want to the following steps to be done by vba of excel. 1. copy ranges of cells in the active worksheet (sheet3) 2. add a new workbook and paste the copied ranges to sheet1 3. save the new workbook with the new file name (e.g. date) 4. close the new workbook and clear the contents the copied ranges of cells in old workbook. following code and make it more simpler? Worksheets("Sheet3").Activate If Range("l1").Value <> "" Then Range("l1").Value = CDate(Date) End If asdate = Range("l1").Value 'date entered by user resp4 = MsgBox(" Case particulars of " & asdate & " will be backed up and deleted from this sheet! Proceed? (Y/N)", vbYesNo, "Deletion of Data") If resp4 = 7 Then Exit Sub
View Replies!
View Related
Vba To Create A New File From Spreadsheet
I have the following spreadsheet: A-------------B File No.-----PRODUCT TYPE 123456-----NDT 123456-----NDT 123456-----NDT 123456--------- 123456--------- 789110--------- 789110--------- 789110--------- What I need is if there is an NDT in Column B and 123456 in Column A then I need to eliminate all rows which contain 123456. End result A 789110 789110 789110
View Replies!
View Related
Create Outlook Appointment From VBA
I am probably overengineering my macro, but I would like to setup an outlook appointment in two peoples calendars from my userform. The macro has access to the email addresses of the selected persons, I just want to send date/time and subject.
View Replies!
View Related
VBA To Create A New Folder From A Textbox Value
I don't know if anyone has tried this but it possible using vba to create a new folder in a directory from the text of a textbox? I have made a userform that I use to collect a persons first name & surname. I then created a folder in a directory manually (e.g Z:spnzphotos) I then scan their photo and place it into the folder. Is it at all possible to have a piece of code that once I press a command button is creates folder ready for me to add the photo to?
View Replies!
View Related
Automate Autofill For PivotTable
I have a document that is exported from an SQL database. Once I have the data I need to throw it into a PivotTable to analyze it. However, the export doesn't fill every cell with the information - instead displaying the cell value once until a new value comes up. Therein, there are often a number of blank cells underneath each entry. I can overcome this by going into the document and autofilling each cell that doesn't have the information in it. However, some of the reports are very large and it is quite time consuming. What I'm after is for a bit of VBA that will autofill the cells until it hits another new cell value then autofill this down until it hits another new cell value, etc. I have attached a dummy copy of the report - one sheet, Raw Data, shows the data as it is exported and the other, Autofilled, shows how I would like the data to be displayed after the code is run.
View Replies!
View Related
Count Of Associated Items In PivotTable
I need to make a pivot table with data from 2 columns. The data has columns named Unit Type and Name. There are 9 different unit types and over a hundred different names. I need to find a way to make a table that counts however many times the name VACANT shows up for each unit type. Is this possible using a pivot table?
View Replies!
View Related
Refresh All PivotTable In Workbook
I am trying to run a macro inside a Worksheet_Change event. Private Sub Worksheet_Change(ByVal Target As Excel. Range) If Not Application.Intersect(Target, Range("Mon_Data")) Is Nothing Then If Target.Value > 0 Then Sub Refresh_PivotTables() ' ' Refresh_PivotTables Macro ' Macro recorded 10/1/2008 by JackChappers ' as you can see, i put the macro within the worksheet_Change event so that, when data is changed on another sheet (target.Value >0), the Macro is run (the macro, by the way, refreshes some Pivot Tables). The refresh Refresh_PivotTables Macro works on it's own, but when i use it like this, i get the message: "Compile error: Ambiguous name detected: Worksheet_Change" If it makes any difference, i also have another Worksheet_Change event above performing another action. I think that may be the problem, if it is, how do i run them both without the error?
View Replies!
View Related
Hide PivotTable Columns
Hide values in Column layout on pivot table i only want to show th total value as its based on quarter figures not per month and this way it looks like Quarterly totals are broken down per month and has to have 200 a mont whenits in fact 200 for the complete quarter, how can i had the 200 for each of the month and just have the total per country? This is what my pivot table looks like county Month Quarterly totals UK June 200 july 200 August 200 Uk Total 200
View Replies!
View Related
Adding Columns Within A Pivottable
I have a data worksheet, which contains about 20,000 lines of journal entry info. There is a separate column for debit and credit, not one "amount" column. On another worksheet, I am aggregating the data to review the impact for this series of journal entries over a time period (12 months). Thus, my pivottable contains "Accounts" in the "ROW" and "Period" in the "COLUMN" sections of the PivotTable Layout. The "Debit" and "Credit" fields are then summed in the "DATA" portion of the PivotTable. My issue revolves around getting the actual impact for each account for each time period. Essentially, I need an extra column inserted between each time period that sums the debits and credits for that period. Do I need a macro for this (not any experience with macros)? My file is way to big to share, so let me know if a pared-down sample would work.
View Replies!
View Related
Preserve Formatting In PivotTable
I have made a pivot table and in Pivot table option Merge labels for colum b. I the problem i am facing is once i give this the next colum text filed is automatically arranged in center. I want the next colum as left alignment. Once i delete some rows in the data then if i refresh it automatically align the next colum to center. I have given the preserve formatting as well as i have un selected the autoformat colum.
View Replies!
View Related
Unhide All PivotTable Fields
In a pivot table the user is able to select multiple entities to hide in the 'Hide Items' field of the PivotTable Fields window. I would like to create a macro (or edit a macro in the VB Editor) that willl reset all 'Hide Items' in the PivotTable Fields window...effectively unselecting any previously hidden entities.
View Replies!
View Related
Vba To Create Separate Reports From Master
I have the following spreadsheet Columns on master sorted by Column J: A--------------E-------------J SUPPLIER----CARRIER------LOC CODE BELGIUM-----HAM-----------BP HYDRO-------MSC-----------BP DAINIPPON---NNR-----------BP BELGIUM------YM-----------BR BELGIUM------YM-----------BR HYDRO-------MSC----------CA BELGIUM-----HAM-----------CA HYDRO-------MSC-----------CA HYDRO-------MSC-----------DE DAINIPPON---NNR-----------DE BELGIUM------YM-----------DE BELGIUM------YM-----------DE What I need too do based on the Codes in Column J is create a separate workbook for each different code.
View Replies!
View Related
VBA Macro To Create New Worksheet, Charts, Etc.
I need a macro to create the following worksheets and charts from an Excel data set: Three (3) worksheets (already created manually in attached Excel file): 1. Chart Data. (a) Column A in Chart Data is always numbered 1 - 600 (50 years x 12 mos/yr). (b) Column I and column Y data sets (from Prod_Month) created in Chart Data. Each data set can be identified and collated with column F in Prod_Month (API) which is unique for each dataset. 2. Rate vs. Month - plot of Daily Gas (col. Y in Prod_Month) vs. Months (col. A in Chart Data). 3. Rate vs. Time - plot of Daily Gas (col. Y in Prod_Month) vs. Calender Time (col. B et. al. in Chart Data) At a minimum, could someone help me create the Chart Data worksheet from the data in PROD_MONTH? Charting all the columns takes time from Chart Data but any data manipulation macro(s) help.
View Replies!
View Related
Can VBA Create A Named Sheet In One Step?
My vb code currently creates a new sheet via "Sheets.add". This new sheet has the default name of "sheet1" which I rename as "tempSheet". I then populate tempSheet with data, save as a CVS file and then I remove tempSheet. If I stay in my workbook and try to execute the code again, the default name of the sheet it now creates is "sheet2" and not "sheet1" - this is a problem becuase my code is trying to rename "sheet1" "tempSheet" and of course it cant.
View Replies!
View Related
VBA - Create Outlook Message With From-field
I have two question for Excel in combination with microsoft outlook: 1. I was wondering if it is possible to create an email from Excel which automatically adds the From-field with a pre-defined emailadres. I am using CreateObject("Outlook.Application"), but I can not add the From-field. 2. I also would like to know if it is possible to do automatically fill the To-field with the emailadress from the user who send me the original file. Maybe this needs some more explanation (sorry my english isnt too good): * A user can download an excel-form from the internet * The form is sent to my emailadress * I give a number to the form * The user will get a message that his question is attend to with the number of the question So I use a macro-button to reply when I have filled in the question-number.
View Replies!
View Related
VBA To Create A Hyperlink Drilldown For Each Cell
I need help in making a VBA to create a looping hyperlink drilldown for each cell in the inventory_general wksht. The purpose of this sheet is to allow a user to click on a hyperlink which will then reference a macro to autofilter the data in the bin_lot worksheet to display all rows for that particular item number. Apparently, you can't select all the cells and insert a hyperlink because all the hyperlinks will reference the first cell value for autofiltering. This is what I tried but it's not working obviously. Sub HyperlinkDrilldown() ' ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ "Bin_Lot!A1", TextToDisplay:=target.value Range("A2:A21").Select End Sub
View Replies!
View Related
Values Appear Twice In PivotTable Row Labels
I have a PivotTable that is summarizing a data table. In the configuration panel, I've dragged one column ("C92") to the "Row Labels" area. Likewise, I've dragged the same column to the "Values" area, where it defaults to "Count of C93". So far, so good. I should mention that my values in column C92 are the whole numbers 0-7 and the string "NA". But in the output chart, each of these values is listed twice! And each count of each value is different! I'm attaching a cut-down version of the spreadsheet that shows this behavior.
View Replies!
View Related
PivotTable Error: Field Name Is Not Valid.
I successfully created two PivotTables two days ago, but when I added more rows of data to the source worksheet I could not refresh either PivotTable view. So after much frustration, I deleted both worksheets and again tried to create a new PivotTable using the wizard. I keep getting this error, and have no idea what it is telling me so that I can go about fixing it: "The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field."
View Replies!
View Related
Show All Items Of PivotTable Field
I have a section of code that takes SO long, but I have to have it. Bascially, I need a pivot field to be set to "All", but there HAS to be a faster way. Here's my Sub FloorCompareSetter() Dim pt As PivotTable Dim pi As PivotItem Set pt = ActiveSheet.PivotTables("PinPointPivot") ' Speeds up code dramatically pt.ManualUpdate = True 'Set the floor comparison for managers, coaches, and reps ' Make sure all PivotItems along line are visible For Each pi In _ pt.PivotFields("Manager").PivotItems pi.Visible = True Next pi pt.ManualUpdate = False End Sub Auto Merged Post Until 24 Hrs Passes;Also, note that I have used other techniques to speed up the process: With Application . ScreenUpdating = False .EnableEvents = False .Calculation = xlManual End With
View Replies!
View Related
Unable To Drag PivotTable Fields
The issue is that for every pivot table in any file running on my machine, I am unable to drag the column or row values to change the sort order - I.e. if the row is Jan/Feb/Mar/etc., I can't drag Jan down to the middle or rearrange them - it will sort by ascending or descending value just fine, but simply won't allow me to rearrange - the 4-way arrow doesn't appear where it should when mousing over the fields. I can drag fields on/off and from/to the column/row/data sum areas, but I simply cannot re-arrange the order of the values (and yes, the Autosort checkbox is set to "manual" in field settings.)
View Replies!
View Related
Link PivotTable Page Fields
I have a workbook with three sheets. The first is a list of employees with their managers. The second is the data about those employees (who they sold product to, and how much, on what date). The third is the PivotTable with the employees' sales. I have the Customer name in the Row field, and the amount in the Data field. I have two page fields - the Manager's name and the Employee's name. The example that I'm using so as not to violate my NDA is a Northwind knockoff. That's why the names probably sound familiar. What I want to do is have the second pivottable page field (Employee) change, so that when I choose a manager's name, only the employees who report to that manager show up in that page field. I have a named range called "allEmp" (all employees), one called "buchanan" (all the employees who report to Steven Buchanan), one called "fuller" (all the employees who report to Andrew Fuller). (File attached, btw). Private Sub Worksheet_Calculate() Dim mgrRange As Range mgrRange = Range("allEmp") Range("$b$1").Select Select Case ActiveCell.Value Case "Andrew Fuller" ActiveWorkbook.PivotTable("EmpSales").PivotFields("Employee").CurrentPage = Range("fuller") Case "Steven Buchanan" ActiveWorkbook.PivotTable("EmpSales").PivotFields("Employee").CurrentPage = Range("buchanan") Case "Susan Erickson"................
View Replies!
View Related
|
TRACKER
