The Simple Machine

Have you noticed that in Excel 2002 or later even if you replace the source data for the pivot table, the pivot table retains items that do not exist anylonger (in the dropdowns)?

For example, some sales reps may leave the company, and the names of their replacements appear in the source table. Even after you refresh the pivot table, the names of the old sales reps will appear, along with the new names.

To prevent missing items from appearing, or clear items that have appeared, you can used the following VBA code:

Sub DeleteMissingItems2002All()
‘prevents unused items in non-OLAP PivotTables
Dim pt As PivotTable
Dim ws As Worksheet
Dim pc As PivotCache

‘change the settings
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next pt
Next ws

‘refresh all the pivot caches
For Each pc In ActiveWorkbook.PivotCaches
On Error Resume Next
pc.Refresh
Next pc

End Sub

If you have excel file with numerous worksheets and you want to get provide a index worksheet with links to each worksheet, then you can utilize the following VBA code.

The goal is to make it much easier to navigate through the excel file with so many worksheets.

To

1) Create a new worksheet

2) List of the name of the worksheets in the excel in in column A with the hyper link.

Sub DebugPrintAllTabs()

Dim sht As Worksheet, i As Integer

ActiveWorkbook.Sheets.Add

i = 1

For Each sht In ActiveWorkbook.Sheets

ActiveSheet.Hyperlinks.Add Anchor:=ActiveSheet.Cells(i, 1), Address:=”", SubAddress:= _

“‘” & sht.Name & “‘!A1″, TextToDisplay:=sht.Name

i = i + 1

Next

End Sub

To

1) Create a new worksheet

2) List of the name of the worksheets in the excel in in column A.

3) provide a hyper link to each worksheet in column B

Sub IndexAllTabs()

Dim sht As Worksheet, i As Integer

ActiveWorkbook.Sheets.Add

i = 1

For Each sht In ActiveWorkbook.Sheets

ActiveSheet.Cells(i, 1) = sht.Name

ActiveSheet.Hyperlinks.Add Anchor:=ActiveSheet.Cells(i, 2), Address:=”", SubAddress:= _

“‘” & sht.Name & “‘!A1″, TextToDisplay:=”Hyperlink”

i = i + 1

Next

End Sub

Say you have multiple excel files (for example daily feeds) in the same format and want to import the data into MS Access. Instead of opening each file and copy pasting the data into Access, the easier way is to use the VBA script below.

Just make sure you create a folder where you keep your excel files and specify it in the code blow by replacing the following line 9.

Also, make sure if you are going to use the folder in the future that you remove the old files.

1. Private Sub Command3_Click()

2. Dim strFile As String ‘Filename

3. Dim strFileList() As String ‘File Array

4. Dim intFile As Integer ‘File Number

5. Dim filename As String

6. Dim path As String

7.

8. DoCmd.SetWarnings False

9. path = “C:\Test\”

10.

11. ‘Loop through the folder & build file list

12. strFile = Dir(path & “*.xls”)

13.

14. While strFile <> “”

15. ‘add files to the list

16. intFile = intFile + 1

17. ReDim Preserve strFileList(1 To intFile)

18. strFileList(intFile) = strFile

19. strFile = Dir()

20. Wend

21.

22. ‘see if any files were found

23. If intFile = 0 Then

24. MsgBox “No files found”

25. End If

26.

27. ‘cycle through the list of files

28. For intFile = 1 To UBound(strFileList)

29. filename = path & strFileList(intFile)

30. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, “tblClientMail”, filename, True

31. Next intFile

32.

33. DoCmd.SetWarnings True

34.

35. End Sub

Unhiding worksheets is a different story, however. Excel only allows you to unhide one sheet at a time. Naturally if you have multiple worksheets you want to unhide, this can be a very tedious task.

Sub UnhideAllSheets()

Dim wsSheet As Worksheet

For Each wsSheet In ActiveWorkbook.Worksheets

wsSheet.Visible = xlSheetVisible

Next wsSheet

End Sub

Sub UnhideSomeSheets()

Dim sSheetName As String

Dim sMessage As String

Dim Msgres As VbMsgBoxResult

For Each wsSheet In ActiveWorkbook.Worksheets

If wsSheet.Visible = xlSheetHidden Then

sSheetName = wsSheet.Name

sMessage = “Unhide the following sheet?” _

& vbNewLine & sSheetName

Msgres = MsgBox(sMessage, vbYesNo)

If Msgres = vbYes Then wsSheet.Visible = xlSheetVisible

End If

Next wsSheet

End Sub