I want to open a spreadsheet and do a pivot table using a macro.
The spreadsheet will be on the desktop and will vary on the file name. therefore, I'd when the macro starts, i'd like to browse for the file, open, then perform the pivot table.
At the end i'd like to display a msgbox showing the date in A2, to confirm the correct report was run.
Exchange (Row Labels), Date (Column Labels), Quantity (Values) – SUM not count
I'd like to copy all data from A5:B (last row with data), not including if A, has a value of (blank).
Sheet1 is the name of the filename (minus the file extension). I'd prefer not to specify, but if i have to, I need to make it this. I need to replace "Dec2013" with whatever Sheet1 may be named (which would be the filename minus extension).
Ive given a go so far. I can get it working up to the point where it opens the file, just need assistance with the Sheet1 part mentioned. then need some help with the actual pivot, and the copy of the data mentioned above.
here is my code thus far:
Dim wsClient As Worksheet
Dim wkbClient As Workbook
Dim wkbThis As Workbook
Dim pivotdate As String
Set wkbThis = ThisWorkbook
' Open Client Workbook
Set wkbClient = OpenWorkbook(True)
' Set wkbClient = OpenWorkbook1()
LetsPivot ' calls pivot table sub
pivotdate = wkbClient.Sheets("Dec2013").Range("A2").Value
MsgBox ("Pivot Table Complete [" & pivotdate & "]. Data copied. Now paste.")
If wkbClient Is Nothing Then
MsgBox "No valid workbook has been provided, Exiting..."
' If we know the worksheet name
' clientWSName = "Foglio1"
' Find the client worksheet by name
' Set wsClient = wkbClient.Sheets(clientWSName)
' This assumes the client worksheet is the first
If (wkbClient.Worksheets.Count > 0) Then
Set wsClient = wkbClient.Worksheets(1)
MsgBox "Unable to process - no worksheet available"
If (wsClient Is Nothing) Then
MsgBox "Null client worksheet..."
' MsgBox "Got a client worksheet: " & wsClient.UsedRange.Count
' Function to open a workbook using the File Dialog
Public Function OpenWorkbook(mode As Boolean) As Excel.Workbook
Dim sFile As String
Dim ShortName As String
Dim autoSecurity As MsoAutomationSecurity
Set OpenWorkbook = Nothing
Dim wkb As Workbook
autoSecurity = Application.AutomationSecurity
Application.AutomationSecurity = msoAutomationSecurityForceDisable
.AllowMultiSelect = False
.Filters.Add "Excel Files", "*.csv"
.FilterIndex = 1
.Title = "Please Select File to open"
If .Show = False Then Exit Function
sFile = .SelectedItems(1)
ShortName = Right(sFile, Len(sFile) - InStrRev(sFile, "\"))
If CheckSourceAvailability(ShortName) Then
Application.EnableEvents = False
If (mode) Then
Set wkb = Workbooks.Open(sFile, ReadOnly:=True, UpdateLinks:=0)
On Error GoTo 0
Set wkb = Workbooks.Open(sFile)
Application.EnableEvents = True
Set OpenWorkbook = wkb
clientFileName = sFile
Application.AutomationSecurity = autoSecurity
If (Err.Number > 0) Then
MsgBox "Error: " & Err.Description & "ErrNo: " & Err.Number
Public Function CheckSourceAvailability(sWorkBook As String) As Boolean
Dim wb As Workbook, bResult As Boolean
bResult = False
For Each wb In Application.Workbooks
If InStr(LCase(wb.Name), LCase(sWorkBook)) > 0 Then
bResult = True
CheckSourceAvailability = bResult
Const SourceSheet As String = ""
Const PivotName As String = "PivotN"
Const TargetCell As String = "A1"
Const ColumnCaptions As String = "Date"
ThisWorkbook.ShowPivotTableFieldList = False