problem with pivot table Visible property in excel 2003 SP2 vba

Visual Basic for Applications (VBA)

Index ‹ Microsoft ISV

- Previous
  - 1
    - A question from a beginner... Hi all: I am new to the programming world, and I am interested in learning VBA. So, please provide some tips where should I start Thanks in advance. Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 2
    - email using CDO (Collaboration Data Objects)? Hey, How do I, from Excel, send email using CDO (Collaboration Data Objects) Best Regards Cathrine Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 3
    - Help needed: Retrieve tables in word and copy them to Excel Hello everyone, I have been assigned a task at work to write a VBA for automation. We have many word documents in a folder that have hundreds of tables within them. I want to create an automation program that is going to retrieve tables from the word document specified and go through the document table by table copying these tables into a separate Excel sheet named after the document itself. As I have never worked with VBA before I was able to get code that finds the tables in a word document but I want the program to create a loop that can copy these tables into a spreadsheet. So far I have this Sub findTable() Dim iResponse As Integer Dim tTable As Table ' Dim ExcelSheet ' Set ExcelSheet = CreateObject("Excel.Application") trying to get an excel sheet open ' ExcelSheet.Visible = True 'If any tables exist, loop through each table in collection. For Each tTable In ActiveDocument.Tables tTable.Select Selection.Copy ExcelSheet.Workbooks.Open ActiveDocument.Path & Application.PathSeparator & "test.xls" ' ThisWorkbook.Activate ' Workbooks.Open "D:\Profiles\RIZWANHA\Desktop\FrameMaker files\test.xls" Selection.Paste 'iResponse = MsgBox("Table found. Find next ", 68) 'If response = vbNo Then Exit For 'User chose to leave search. Next MsgBox prompt:="Search Complete.", buttons:=vbInformation End Sub I am getting errors when I try pasting into the excel spreadsheet. Your help will be appreciated. Thanks Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 4
    - Creating LookUp/Filter Command Button In Access 2003 using VB I'm trying to create a LookUp/Filter Command Button using VB. I am not a programmer so I tried using what I found on the internet. My Field Name is: Last Name My Table Field Name is also: Last Name The code that I am using is as follows: Me.Filter = "Last Name = "" & me.Last_Name & "'" Me.FilterOn = True When I click on the Command Button I get the following error: The expression ON Click you entered is the event property setting producing the following error: Syntax error. Can someone please tell me how I would do this correctly Thanks Kay Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 5
    - Office 10 \ winword.exe runtime error I have downloaded the service pack 3 but I'm still encountering the problem when I try to open MS Word. MS Excel, MS Outlook , MS Powerpoint & MS Viso all work fine. Any time I try to open MS word I receice a Box vissual c++ runtime library. This application has requested the runtime to terminate it in an unusaul way. Microsoft Office\Office 10\Winword.exe. Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 6
    - Trying to use XML Schema to import datas Hello all! well, I've been trying to importing some datas of a XML document based in rules XML schema to an Acess Table. What I found about it was only the code Application. ImportXML _ DataSource:="employees. xml ", _ ImportOptions:=acStructureAndData then I used this Application.ImportXML _ DataSource:="d:\schema\users.xml", _ SchemaSource:="d:\schema\users.xsd", _ DataTarget:="Users_Main" No sucess. I have no idea how to do this!! any help is appreciate... ThanX all Marcos Hercules Santos Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 7
    - Hiding Access Application Window Hi Everyone! Apologies in advance for the long thread! I have an Access database, which I have developed to monitor and track assets issued to employees, and it is intended to be used by others, as opposed to myself. It therefore makes heavy use of forms in order to provide a friendly user interface for non-technical people. To really polish the application off, I want to be able to completely hide the main Access application window, so from a user's point of view, they would not know it was Access. I have already looked extensively into doing this and the common approach seems to be calling the Windows API: ShowWindow function. I've seen various snippets of code etc on how to do this, but I'm still not 100% sure how it would work with my particular application. My application consists of a main form, which users can access all the various functionality. This form has been set to load as soon as the database is opened from the Tools>Startup menu, and will be open the whole time the application is in use. Its modal and popup properties are set to False. All other forms are in effect children of this main form and have their modal and pop up properties set to true, and display on top of the main form as and when they're called. I also have a login form, which I've set to open from the Load event of the main form. This is obviously modal and will quit the application if closed, or if the user fails to log on successfully. Given this setup, how can I successfully use the Windows API mentioned above, if indeed there are no better solutions Initial trials have all resulted in successfully being able to hide the main Access App window, but I get things like the login window appears but the main form is nowhere to be seen, or the main form appears, but the login form can't be seen. In both cases, I had to end the process via Ctrl Alt + Del. Cheers for any help and sorry for the long winded thread!! Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 8
    - Assigning keyboard shortcut to VBA macro programmatically Using OLE, I create an Excel sheet with graphs and some macros to manipulate them. I'd like to assign keyboard shotcuts to the macros, so that the user has easy access to them. I can't figure out - however - how to do that. Any suggestions Thanks in advance. Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 9
    - Browse hard disk with VBA Hello, I want to read a text file into Excel with VBA. This all works fine with some code I found, the only problem is that the text file I want to open should either be written in the code, or in a textbox but than I have to type the complete location (C:\MyDocuments....) what I would like to have is a browser, similar to the one from Windows on "Ctrl O", which opens when I push a button so I can browse for the file on my hard disk and select the file I want to read into Excel. Does anybody know if this could be made in VBA, and if it is possible how Barry Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 10
    - Microsoft Document Image Printer - MODI Registry Settings Does anyone know what are all the available registry settings for MODI Through much searching in google (msn search finds nothing) I've found the following the following ' Write to File Only (uses current save folder and Note####.mdi as file name) SetKeyValue HKEY_CURRENT_USER, "Software\Microsoft\Office\11.0\MODI\MDI Writer", "PrivateFlags", 1, REG_DWORD ' Don't Show Popup Window SetKeyValue HKEY_CURRENT_USER, "Software\Microsoft\Office\11.0\MODI\MDI Writer", "OpenInMODI", 0, REG_DWORD 'Set persistent annotations SetKeyValue HKEY_CURRENT_USER, "Software\Microsoft\Office\11.0\MODI\MDI Writer", "BurnInAnnotations", 5, REG_DWORD Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 11
    - How to open a folder through excel macro! Hi, I am a new learner in Macro field. I want to know how we can open a folder using macro. The scenario is ---> I want to open a folder having a no of files . After that i want to open these files one by one....... Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 12
    - Application-defined or object-defined error Hello All, I'm receiving the error message in subject in the following code: Public Sub Err(ErrorID As Long , _ Optional AddInfo As String = "", _ Optional Source As String = "", _ Optional Log As Boolean = True ) If Log = True Then Me.Log MsgTypeError, ErrorID, AddInfo, Source MsgBox Me.GetErrorDescription(ErrorID), vbCritical + vbOKOnly , "FTCM Error" End <= this is where the error occurs End Sub VBA.Err object is clean the code lies in worksheet in Excel 2002 named "Log" VBA.Err.Raise(Number:=513, Source:="Anything", Description:="Whatever") also raises the same error (not "Whatever" as I'd expect) I know I have overriden names "Err" and "Log", but renaming does not solve anything Edit 2007-10-29 17:24: If I remove the "End" statement and place it into calling procedure after the Log.Err call, everything works as expected. Does anyone have an idea, why this happens Thanks in advance. Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 13
    - Subscript out of range I am new to VBA and am trying to create a copy of a sheet from one workbook to another workbook using the following code: Workbooks.Open Filename:=bw_path & "\" & bw_file 'Opening the file from which I want to copy. Sheets(bw_sh).Copy After:=ThisWorkbook.Sheets(3) 'Trying to copy the open worksheet into the workbook housing the macro. I am getting the "subscript out of range" error message. I inherited this code and it used to work before. What am I doing wrong and what would be a suggested solution Any help will be appreciated Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 14
    - Application.FileSearch in Excel 2007 There is an issue which I am facing after I installed Office 2007. I had written a small VBA which would search for a file and if found will rename the file. I used Application.FileSearch then. Now I am tying to do it again but unfortunately there is an error prompted stating that 'the object does not exist' Set fSearch = Application.FileSearch defPath = "H:\SourceSafe_1_Feb-28_Feb_2006\SecondSet" Set rg = Range("MyFiles") fSearch.LookIn = defPath I have been frantically looking for changes in the VBA object model especially for Excel but cant seem to find them. Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 15
    - Can you use VBA to access a spread sheet embedded in a word doc? I am trying to use VBA code written in MSWord to access an embedded spread sheet. I can't find any documentation on how this is suppose to work. Does word access the sheet through an instance of excel Is the sheet part of the document so I can find it in the "ThisDocument " I have tried everything I can think of, so any help would be appreciated. Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 16
    - Excel - Resize image to specified target cells' dimensions I have a command button on a worksheet that calls up the ＆getsaveasfilename＊ dialog, and inserts the selected image file into a merged range of cells. The images are automatically re-sized to fit into the cell (aspect ratio maintained). Everything works well, but I ran into a problem. There are about 70 sheets in the workbook, and this command button can be found about 250 times throughout the workbook. I found out that the merged cells to contain the images vary in width and height throughout the workbook, even on the same sheet. (By the way, I did not create the sheet, I was just asked to input the commands 每 lucky me) I do not want to go through every one and figure out the width and height for each cell on the worksheet and type in the corresponding values for every button, (it might take me weeks). What would be helpful is if there were a way to write the code so that the image is resized to the size of the cell (it is actually a merged set of cells), that it is being placed into. Essentially I need to maintain the aspect ratio of the image file, and fill the target cell(s) without exceeding their boundaries. Attached is the code in it＊s present state＃ Code Snippet Private Sub CommandButton4_Click() 'before ActiveSheet.Unprotect Range("A9").Select Dim PicLocation As String PicLocation = Application.GetSaveAsFilename("C:\Windows\My Documents", "Image Files (*.jpg),*.jpg", , "Specify Image Location") If PicLocation <> "False" Then ActiveSheet.Pictures.Insert(PicLocation).Select Else Exit Sub End If Selection.ShapeRange.LockAspectRatio = msoTrue Selection.ShapeRange.Width = 235# Selection.ShapeRange.Height = 165# With Selection .Placement = xlMoveAndSize .PrintObject = True End With Range("H16").Select 'after ActiveSheet.Protect End Sub Any ideas on how to fix my dilemma Tag: problem with pivot table Visible property in excel 2003 SP2 vba
- Next
  - 1
    - Subscript out of range with a simple sql :-( [newbie to VBA] Hi all, can anyone assist on this one (I'm newbie to VBA) I'm trying to get the list of items from mySql (I have a successful connection) for some reason I get this error message: Run-time error '9': Subscript out of range. thanks for any pointers! Code Snippet Private Sub btnTestReadInMysqlData_Click() Dim objDB, arrRecord, strRecord, strOutput Dim oRS, nRec, oFld Dim row 'Database connection & select all from Table Set objDB = DBConnect() Set oRS = objDB.Execute("SELECT * FROM `production` WHERE id<1000000") 'Dump Records from Table nRec = 0 row = 1 Do While Not oRS.EOF For Each oFld In oRS.Fields 'WScript.Echo oFld.Name & " = " & oFld.Value Worksheets("Tabelle1").Cells(row, 1).Value = oFld.Value 'increment row = row + 1 Next oRS.MoveNext Loop End Sub Function DBConnect() Set objDB = CreateObject("ADODB.Connection") objDB.Open "ago_usgroups" Set DBConnect = objDB End Function Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 2
    - Run-time error '13': Type Mismatch *ignore this ^^!* i always get this error when i try to run...does anyone know what's the problem Code Snippet Option Explicit Private Sub UserForm_Initialize() UpdateData cbxDept.AddItem "Marketing & Sales" cbxDept.AddItem "Supply Chain" cbxDept.AddItem "Purchasing" cbxDept.AddItem "Technical & Production" cbxDept.AddItem "Research & Development" cbxDept.AddItem "Finance & Control" cbxDept.AddItem "Human Resource" cbxDept.AddItem "Globe & ISIT Services" End Sub Private Sub cmdPrevious_Click() If txtRow.Text > 1 Then txtRow.Velue = txtRow.Value - 1 UpdateData End Sub Private Sub cmdNext_Click() If txtRow.Text < Range("Database").Rows.Count Then txtRow.Value = txtRow.Value + 1 UpdateData End Sub Private Sub txtFName_AfterUpdate() Range("A2")(txtRow.Text).Value = txtFName.Value End Sub Private Sub txtLName_AfterUpdate() Range("B2")(txtRow.Text).Value = txtLastName.Value End Sub Private Sub cbxDept_AfterUpdate() Range("C2")(txtRow.Text).Value = cbxDept.Value End Sub Private Sub txtRow_AfterUpdate() If txtRow.Text < 1 Then txtRow.Text = 1 ElseIf txtRow.Text > Range("Database").Rows.Count Then txtRow.Text = Range("Database").Rows.Count End If UpdateData txtFName.SetFocus End Sub Private Sub UpdateData() txtFName.Text = Range("A2")(txtRow.Text).Value txtLName.Text = Range("B2")(txtRow.Text).Value cbxDept.DropButtonStyle = Range("C2")(txtRow.Text).Value End Sub thanx Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 3
    - Microsoft Office Document Image Writer driver Hi, I'm having the following problem with my installation of Microsoft Office Professional Edition 2003 on my Windows XP Professional operating system. I am trying to send a fax using my internet fax service, so I am in Microsoft Word, and click on File/Send to/Recipient Using Internet Fax Service..." and I get the following error message: "The Microsoft Office Document Image Writer driver cannot be detected. To use the Internet fax service, run the Office Setup Program to remove the driver, and then run the Office Setup Program to add the driver back." In short, I did that and it didn't work. It still gives me the same message. Can anybody help me please Thanks. Rich Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 4
    - CRM 3.0 Outlook Client - Missing Price List I have a question regarding the settings necessary for the CRM 3.0 Outlook client to be installed. I have the CRM and Exchange on seperate servers & networks, both running SBS 2003. I have the CRM server setup as localhost in my office, and Exchange is hosted off-site. Whenever I open outlook I am getting errors saying: "Your Web browser options are currently set to disable cookies. Microsoft CRM requires cookies to use the Web client. To enable cookies, see the online Help for your browser" "An error occurred loading Microsoft CRM functionality. Try restarting Microsoft Outlook. Contact your system administrator if errors persist." I have allowed all cookies in IE 7, and still get the first error. When I install the CRM Outlook client (I've tried laptop and desktop versions), it does not ask me for login information. Do CRM and Exchange have to be on the same network using the same Active Directory Does CRM assume that it will be using the same login information as used for exchange Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 5
    - Problem: Macro not deleting columns I select Hi all, I have a section of code in my macro which tells it to delete a selection of columns: Columns("C:F").Select Selection.Delete Shift:=xlToLeft this is the first bit of code in my macro but isn't working correctly. Instead of deleting columns C to F its deleting columns A to H. Any ideas why this could be happening Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 6
    - Using VBA in Word to slect and print a particular Record (recipient) Hi, I am trying to set up Word so that I can get information from Excel and input this data into a form letter. I am using Mail Merge and that part works great. But now I want to be able to search for a particular record (recipient) and print the document only for that record. I know you can search through the "find" button on the mail merge toolbar but I want to make this more user friendly because my fellow emplyees are not very familiar with the mail merge function and it woukld be easier to go through a macro. Is there someway i can do this through VBA Like have the user enter a string into a form with the name of the person they are trying to find and then press a button and have it print Thanks. JB Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 7
    - sending emails from MS Word with variable subject line Hi, I've searched these forums but have not been able to find exactly what I need. I know how to send emails with VBA in Word, but the only problem I have is that as far as i'm aware the subject line is always fixed in the code ( .Subject = "New subject" ) What i'm trying to do, is send reports i have reviewed to an email address by clicking a button in the toolbar. The email address is always the same but the subject line shows the customer's name and his account number (so that's different all the time) Is there any way I can get the VBA code to copy the first line of the report (which has the customer's account number and name) and use that in the subject line Many thanks for your help in advance. Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 8
    - A question on the ISV/Software Solutions competency customer reference requirements Could you please clarify the ISV/Software Solutions competency requirements for customer references The ※Competency requirements§ web page ( https://partner.microsoft.com/40012621 ) reads: ※Submit at least three customer references for tested packaged software solutions. The solution must be a business application, deployed for a minimum of five users.§ I have the following questions related to the quote above: Do the references have to be provided for the same software product that has undergone the ISV Testing Framework testing and received a Product ID from third party testing vendor If the answer to "1" is true, does this indeed limit the kind of software product to multi-user business applications If the answer to "1" is false, does the software product(s) for which the customer references are provided also have to be tested according to the ISV Testing Framework Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 9
    - Easy msgbox Query Here's what I'm looking to do in Excel: User inputs data in cell C7 A function in cell C11 calculates A function in cell C12 calculates I want to ensure that both C11 and C12 are positive based on C7 input If they are not, I want a msgbox to appear saying, "Enter a Larger Value." I used the Change event over the Calculate event because it's my understanding that the Calculate event will not work with functions. Here's what I've got so far: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Range("C11:C12") < 0 then MsgBox "Enter Larger Value." End If End Sub Can anyone help Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 10
    - Getting word to understand html Hi all!, I have some html content and want word to render it.. I'm using the word.application object, creating a new document and then using selection.typetext "<h1>test123</h1>".. That doesn't work as I want, it get typed exactly like that. I want word to understand that it should use headersize 1 and type only test123.. How to do that , would be nice with something like selection.typehtml Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 11
    - Problem with CWnd::RunModalLoop(DWORD dwFlags) in wincore.cpp Hello, We have created a VC++ application that has 2 menu options. - submenu 1 opens the VBA IDE - submenu 2 opens a Modal Dialog to execute a functionality (say FUNC1) Whenever we directly select submenu 2 the application executes properly. But the application hangs whenever we select submenu 1 (opening the VBA IDE) and then select submenu 2 (having the VBA IDE still open) On debugging we find that in the problematic case (in which the application hangs), the control fails to come out of an infinite loop inside the mfc source code wincore.cpp The details of the code flow are given below for this problematic case: The modal dialog is created using the function INT_PTR CDialog::DoModal() (present in mfc source code dlgcore.cpp) which inturn calls int CWnd::RunModalLoop(DWORD dwFlags) present in the mfc source code wincore.cpp (elaborated at the end of this message). The control gets struck in this function (which has an infinite loop inside it) thereby causing the application to hang. We are unable to understand the reason for this code behaviour and would like to clarify whether there is any bug in the MFC source due to which this occurs. Has anyone faced such similar problems while creating Modal Dialogs . If there is any work around to tackle this problem we would be glad to know. Thanks in advance, Ravi. --------------------------------------------------------------- (source code details of function RunModalLoop present in wincore.cpp, Courtesy : Microsoft corp.) int CWnd::RunModalLoop(DWORD dwFlags) { ASSERT(::IsWindow(m_hWnd)); // window must be created ASSERT(!(m_nFlags & WF_MODALLOOP)); // window must not already be in modal state // for tracking the idle time state BOOL bIdle = TRUE; LONG lIdleCount = 0; BOOL bShowIdle = (dwFlags & MLF_SHOWONIDLE) && !(GetStyle() & WS_VISIBLE); HWND hWndParent = ::GetParent(m_hWnd); m_nFlags |= (WF_MODALLOOP|WF_CONTINUEMODAL); MSG *pMsg = AfxGetCurrentMessage(); // acquire and dispatch messages until the modal state is done for (;;) <---- (the application fails to exit this infinite loop) { ASSERT(ContinueModal()); // phase1: check to see if we can do idle work while (bIdle && !::PeekMessage(pMsg, NULL, NULL, NULL, PM_NOREMOVE)) { ASSERT(ContinueModal()); // show the dialog when the message queue goes idle if (bShowIdle) { ShowWindow(SW_SHOWNORMAL); UpdateWindow(); bShowIdle = FALSE; } // call OnIdle while in bIdle state if (!(dwFlags & MLF_NOIDLEMSG) && hWndParent != NULL && lIdleCount == 0) { // send WM_ENTERIDLE to the parent ::SendMessage(hWndParent, WM_ENTERIDLE, MSGF_DIALOGBOX, (LPARAM)m_hWnd); } if ((dwFlags & MLF_NOKICKIDLE) || !SendMessage(WM_KICKIDLE, MSGF_DIALOGBOX, lIdleCount++)) { // stop idle processing next time bIdle = FALSE; } } // phase2: pump messages while available do { ASSERT(ContinueModal()); // pump message, but quit on WM_QUIT if (!AfxPumpMessage()) { AfxPostQuitMessage(0); return -1; } // show the window when certain special messages rec'd if (bShowIdle && (pMsg->message == 0x118 || pMsg->message == WM_SYSKEYDOWN)) { ShowWindow(SW_SHOWNORMAL); UpdateWindow(); bShowIdle = FALSE; } if (!ContinueModal()) goto ExitModal; // reset "no idle" state after pumping "normal" message if (AfxIsIdleMessage(pMsg)) { bIdle = TRUE; lIdleCount = 0; } } while (::PeekMessage(pMsg, NULL, NULL, NULL, PM_NOREMOVE)); } ExitModal: m_nFlags &= ~(WF_MODALLOOP|WF_CONTINUEMODAL); return m_nModalResult; } -------------------------------------------------------------- Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 12
    - inserting row in excel I'm using vb.net express to call excel with this code Private Sub Button3_Click( ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click Dim XL As Excel.Application Dim xlwb As Excel.Workbook Dim xlsheet As Excel.Worksheet XL = New Excel.Application xlwb = XL.Workbooks.Open( "C:\Documents and Settings\S. Ross\My Documents\sales invoice1.xls" ) xlsheet = xlwb.Worksheets(1) XL.Visible = False Try xlsheet.Cells(8, 2).select() XL.Selection.value = Me .NameComboBox.Text.ToString xlsheet.Cells(9, 2).select() XL.Selection.value = Me .AddressTextBox.Text.ToString xlsheet.Cells(10, 2).select() XL.Selection.value = ( Me .CityTextBox.Text.ToString) xlsheet.Cells(10, 3).select() XL.Selection.value = ( Me .StateTextBox.Text.ToString) xlsheet.Cells(11, 2).select() XL.Selection.value = Me .ZipTextBox.Text.ToString xlsheet.Cells(4, 9).select() XL.Selection.value = Me .InvoiceIDTextBox.Text.ToString xlsheet.Cells(5, 9).select() XL.Selection.value = Me .CustomerIDTextBox.Text.ToString xlsheet.Cells(3, 9).select() XL.Selection.value = DateTime.Today Catch ex As Exception End Try Try Dim iddatatables As DataTable = Me .RCSDataSet.InvoiceDetails Dim idx As Integer = 1 For idx = 0 To iddatatables.Columns.Count - 1 Next Dim row As DataRow Dim rowidx As Integer = 15 For Each row In iddatatables.Rows For idx = 0 To iddatatables.Columns.Count - 1 xlsheet.Cells(rowidx, idx + 1) = row(idx) Next rowidx = rowidx + 1 Next Catch End Try xlsheet.PrintOut() xlsheet = Nothing xlwb = Nothing XL.Quit() XL = Nothing GC.Collect() End Sub This prints the required invoice but only to row 36 on the excel worksheet, if I have more than 36 rows to print I need to be able to insert the rows at row 36 and push the rest of the rows down Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 13
    - How to write code for dynamically (programatically) generated combo boxes (excel-shape object) in excel? How to write code for dynamically (programatically) generated combo boxes (excel-shape object) in excel If the combo boxes are named using the cell reference on which these combo boxes are placed and linked. For example, a combo box, placed over the cell "G23", will be named as G23. Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 14
    - ACCESS - You do not have exclusive access to the database I have an ACCESS application that is running locally on a user's machine. When they log in, they get the error "You do not have exclusive access to the database", then they have to click "OK" 21 times before the application will open up for them. The .mdw file is named different from the .mdb file, so they aren't locking each other out. Any ideas Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 15
    - Vba Excel 03 - Detect Shape Position and calculte related row number when sort / filter is activated Hi guys, I have an excel datasheet where each row has on it a Shape called "myShape" + the Number of the Row where it is located, in example: Tag: problem with pivot table Visible property in excel 2003 SP2 vba
  - 16
    - Are these valid bucket ids ? Hi Jason, Saar, The reports at my end is having following bucket id : 456012092, 455976259, 455999458, 49984945. But these reports are not available in Winqual - atleast I can't see when I log in to Winqual. One of them is as old as 6 days. Others are one day. Could you please, confirm whether these are all valid bucket id If not then reason. Thanks, DHON Tag: problem with pivot table Visible property in excel 2003 SP2 vba

Jari Haaranen

Hi there,

I have problem with pivot table Visible property in excel 2003 SP2 vba."run-time error 1004: Unable to Set Visible Property"
VBA code have a Pivot Item property for filtering pivot data.
Pivot item is "DeliveryDay". In code i check that Visible value of items (true/false) and if it is False then i like to change it to True.
Code using For Each loop for checking Visible proberty value. (If PivItem.Visible = False Then PivItem.Visible = True)
I'am really sure that i have right syntax in my code. I'am using similar code in other PivotSheet and it's works :-|

Following article tells at that is an Excel bug. I don't have any sorting in pivotTable. Article is over one year old, so i loaded SP2 for this case -> No help.

Is there any hotfix/solution for this case

"Is an Excel bug that set PivotItem.Visible to true will generate 1004 Macro Error
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=168090&SiteID=1"

cheers, Jari

Re: problem with pivot table Visible property in excel 2003 SP2 vba

ADG

Hi

can you post your code, just in case there is an obvious error / typo

Regards

ADG

Re: problem with pivot table Visible property in excel 2003 SP2 vba

Jari Haaranen

Hi ADG,

This problem has been solved. I still don't understand why following (Problem code) won't work.

thanks anyway, jha

Sub filterOpenBeDelayedData(pivotname As String, currentmonth As String, currentyear As Integer)

Dim PivItem As PivotItem
Sheets("OpenBeDelayed").Select

' *** Problem code
' For Each PivItem In _
'ActiveSheet.PivotTables(pivotname).PivotFields("DeliveryDay").PivotItems
' If PivItem.Visible = False Then
' PivItem.Visible = True
' End If
'Next PivItem

' *** Problem code end

For Each PivItem In _
ActiveSheet.PivotTables(pivotname).PivotFields("DeliveryDay").PivotItems

If CDate(PivItem.Name) < Date Then
If PivItem.Visible = False Then
PivItem.Visible = True
End If
End If
If CDate(PivItem.Name) >= Date Then
PivItem.Visible = False
End If

Next PivItem

End Sub