Posts by X-man

    Re: Delete ImportError TABLES

    Thanks Alan,

    Created a new module

    Function deladdresserrors() As Boolean
    Dim tdef As tabledef
    For Each tdef In CurrentDb.tabledefs
        If Left(tdef.Name, 20) = "address_Importerrors" Then
            End If
    End Function

    but it does not seem to work because I am certainly wrong defining the function as boolean. How should I define it?

    My macro is currently doing this:

    1)runs a macro to pull a range called "address" from lets say 4 spreadsheets to a table called "address table" (DONE)
    2)a deletequery deletes blank records from the "address table" (DONE)
    3)here I would run your code to clear the "address_importerrors" ( HELP! )
    4)MSGBOX confirmation (DONE)

    Appreciate your help

    I have a Access code that pulls data out from several excel spreadsheets. It works great but if I request data from 4 spreadsheets, access will create tables called.


    I have a Delete Query to eliminate the records I do not want but how could I delete all these Importerror tables?

    Hello All,

    This is my problem

    My first spreadsheet has a Dynamic Range called "DetailsList"


    What I want to do is go to a second spreadsheet and query this range using the "Get External Data" option but sadly "DetailsList" or any other dynamic ranges do not appear on the list to import.

    Could you please suggest me something,


    Ok, this is the code to create Word documets named after each page field in the excel pivot table.

    Remember to activate in VBA the Microsoft Word Object Library (Tools>Preferences)

    Hope everything is clear

    Option Explicit
    Sub CyclePages()

    Dim p As PivotItem
    Dim wdapp As Word.Application
    Dim wddoc As Document
    Dim wdinvrng As Word.Range
    Dim Doc As String

    With ActiveSheet.PivotTables(1).PivotCache.Refresh
    End With

    With ActiveSheet.PivotTables(1).PageFields(1)

    For Each p In .PivotItems
    .CurrentPage = "" & p & ""

    ‘will use every item in the page field to name the individual word doc.
    ‘The folder must be created beforehand
    Doc = "C:Individual Docs" & p & ".doc"
    Set wdapp = New Word.Application
    Set wddoc = wdapp.Documents.Add
    ‘I need to copy columns a to c
    Set wdinvrng = wddoc.Content
    wdinvrng.Collapse wdCollapseEnd
    wdinvrng.PasteSpecial link:=False, DataType:=wdPasteMetafilePicture, Placement:=wdInLine, DisplayAsIcon:=False

    wddoc.SaveAs (Doc)

    Set wdapp = Nothing
    Application.CutCopyMode = False

    Next p

    End With
    End Sub

    Ok, with your debug tip found out that I was trying to create a file called

    country / subsidiary.doc

    Obviously I could not use the slash as a name of the document. That is fixed, corrected the data and not the pivot has a list like this

    country - subsidiary

    but the code still does not run. now is giving me an error " 1004: unable to set the _default property of the pivotitem class" on the line

    .CurrentPage = "" & p & ""

    your debug procedure shows me that the value in P has not change and is still

    P = country / subsidiary

    I have refreshed the tables but thevalue in p has not changed,

    How do I reset the P value?

    The code runs until the line

    .SaveAs Filename:=Doc, FileFormat:=wdFormatDocument

    Then that error says that is not a valid file name

    When I state that

    Doc = C:\test.doc

    the code works fine, so I suppose all is down to the definition of the file name using the p variable.

    Thanks Derk,

    Your code works great and gave me the great idea to use your "p" string as the name of the word documets i am creating. I am using the code bellow but it keep giving me this error

    run time error 5152

    Please correct my lines

    Set wdApp = CreateObject("word.application")
    Set wdDoc = wdApp.Documents.Add(documenttype:=wdNewBlankDocument)

    Doc = "C:\Allocation Invoices\" & p & ".doc"

    With wdApp.ActiveDocument
    .SaveAs Filename:=Doc, FileFormat:=wdFormatDocument
    End With


    I have created a PivotTable that is going to be emailed as a word attachment. I would like to do what the “Show Pages” option does, BUT opening a word document for every page I have.

    I tried to change the field with the recorder but it did not work.

    So my mission is, considering I have “ALL” in the page field the code will do this:

    1)Select the next item in the page field
    2)Select the Pivot Range (the Table plus a few lines above it)
    3)Do Copy
    4)Open a new Word Document
    5)Paste as Picture
    6)Go back to Excel and
    7)Repeat step 1

    At the moment I am doing this manually (92 Times!) but I am sure you will give me a hand with this.

    Thanks for your help

    Thanks so much Iridium and Dave,

    I went for the last code because I have several Pivottables, but they are not only in the "ACTIVESHEET".

    So my macro runs clicking a picture in activesheet. Then copied the code again so it runs the update on sheets2 and 5. Might not look so efficient but is doing a great job.

    Thank you guys once again.

    Sub UpdateAll()
    Dim pt As PivotTable
    With ActiveSheet
    .Protect Password:="Secret", UserInterfaceOnly:=True
    For Each pt In .PivotTables
    Next pt
    End With

    With Sheet2
    .Protect Password:="Secret", UserInterfaceOnly:=True
    For Each pt In .PivotTables
    Next pt
    End With

    With Sheet5
    .Protect Password:="Secret", UserInterfaceOnly:=True
    For Each pt In .PivotTables
    Next pt
    End With

    End Sub

    My VBA code password protects all sheets every time I open this Spreadsheet.
    But when I try to refresh the data, I get an error "Cannot Edit Pivottable on protected sheet"
    How could I, using VBA,
    1)unlock the sheets
    2)refresh the Povottables
    3)lock the sheets again

    Thanks in advance

    Hello Ed and Brandtrock,

    I have an Excell Worksheet that requires a "passwordA" to open it. The user has access to this one.
    The sheets inside and VBA code are protected with "PasswordB" that only I know, cos I am the developer =)
    The user can open the workbook, input data and run the macros but can not delete, modify or see the formulas/code.
    As you see I am using as much security as I can, but I am sure that one of those cracks could remove the passwords and expose all my hard work =(
    So I am looking into do something else to make it harder to crack. Do you have any suggestion?

    PS: Thanks for the flash responses:spin: