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
        curentdb.tabledefs(tdef.Name).Delete
        End If
    Next
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

Hi,
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.

Address_Importerror
Address_Importerror1
Address_Importerror2
Address_Importerror3

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"

=OFFSET(Sheet1!$B$3,0,0,COUNTA(Sheet1!$B$3:$B$1000),1)

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,

Thanks

May I suggest creating a Pivot Table and then use the "show pages" Option of it

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
ActiveSheet.Range("A:C").Copy
Set wdinvrng = wddoc.Content
wdinvrng.Collapse wdCollapseEnd
wdinvrng.InsertParagraphAfter
wdinvrng.PasteSpecial link:=False, DataType:=wdPasteMetafilePicture, Placement:=wdInLine, DisplayAsIcon:=False


wddoc.SaveAs (Doc)
wdapp.Quit

Set wdapp = Nothing
Application.CutCopyMode = False


Next p


End With
End Sub

Select the range and do copy, then go to word and do "paste special" and "picture"

Still having problems copying from excel and pasting the it into word =s

Problem sorted! Deleted the old tabel and created a new one THAT IS NOT BASED ON ANOTHER TABLE. Runs beautifully. I will post the final code asap.

Thanks again

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.

Yes Derk, I noticed that and also tryed using the extra ""s but for some reason it does not work.

Thank for your help anyway I will post my code once I finish... if I get a solution

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
.Close
End With

wdApp.Quit

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
pt.RefreshTable
Next pt
End With

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

With Sheet5
.Protect Password:="Secret", UserInterfaceOnly:=True
For Each pt In .PivotTables
pt.RefreshTable
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

Great tip guys!!!

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:

I know you can protect the spreadsheet using Excel's own method, but I am wondering if you know how could I use code to create a stronger protection.