- Click "Start", than click on "Run" click.
- Type "cmd" than press enter.
- Go to the directory of your USB flash drive.
- Type "ATTRIB -S -H *.* /S /D" and press enter and wait for a minute.
How to Retrieve Lost Files in USB Flash Drive
Useful Qlikview Macros
Run external program
FUNCTION RunExe(cmd)
CreateObject("WScript.Shell").Exec(cmd)
END FUNCTION
SUB CallExample
RunExe("c:\Program Files\Internet Explorer\iexplore.exe")
END SUB
Export object to Excel
FUNCTION ExcelExport(objID)
set obj = ActiveDocument.GetSheetObject( objID )
w = obj.GetColumnCount
if obj.GetRowCount>1001 then
h=1000
else
h=obj.GetRowCount
end if
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add
objExcel.Worksheets(1).select()
objExcel.Visible = True
set CellMatrix = obj.GetCells2(0,0,w,h)
column = 1
for cc=0 to w-1
objExcel.Cells(1,column).Value = CellMatrix(0)(cc).Text objExcel.Cells(1,column).EntireRow.Font.Bold = True
column = column +1
next c = 1
r =2
for RowIter=1 to h-1
for ColIter=0 to w-1
objExcel.Cells(r,c).Value = CellMatrix(RowIter)(ColIter).Text
c = c +1
next r = r+1
c = 1
next
END FUNCTION
SUB CallExample ExcelExport( "CH01" )
END SUB
Export object to JPG
FUNCTION ExportObjectToJpg( ObjID, fName)
ActiveDocument.GetSheetObject(ObjID).ExportBitmapToFile
fName
END FUNCTION
SUB CallExample
ExportObjectToJpg "CH01", "C:\CH01Image.jpg"
END SUB
Save and exit QlikView
SUB SaveAndQuit ActiveDocument.Save
ActiveDocument.GetApplication.Quit
END SUB
Clone Dimension Group
SUB DuplicateGroups
SourceGroup = InputBox("Enter Source Group Name")
CopiesNo = InputBox("How many copies?")
SourceGroupProperties = ActiveDocument.GetGroup(SourceGroup).GetProperties
FOR i = 1 TO CopiesNo
SET DestinationGroup = ActiveDocument.CreateGroup(SourceGroupProperties.Name & "_" & i)
SET DestinationGroupProperties = DestinationGroup.GetProperties
IF SourceGroupProperties.IsCyclic THEN
DestinationGroupProperties.IsCyclic = true
DestinationGroup.SetProperties
DestinationGroupProperties
ELSE
SourceGroupProperties.IsCyclic = true
DestinationGroupProperties.SetProperties
SourceGroupProperties
END IF
SET Fields = SourceGroupProperties.FieldDefs
FOR c = 0 TO Fields.Count-1
SET fld = Fields(c)
DestinationGroup.AddField
fld.name
NEXT
Application.waitforidle
NEXT
END SUB
Open document with selection of current month
SUB DocumentOpen
ActiveDocument.Sheets("Intro").Activate
ActiveDocument.ClearAll (true)
ActiveDocument.Fields("YearMonth").Select
ActiveDocument.Evaluate("Date(MonthStart(Today(), 0),'MMM-YYYY')")
END SUB
Read and Write variables
FUNCTION getVariable(varName)
set v = ActiveDocument.Variables(varName)
getVariable = v.GetContent.String
END FUNCTION
SUB setVariable(varName, varValue)
set v = ActiveDocument.Variables(varName)
v.SetContent varValue, true
END SUB
Open QlikView application, reload, press a button and close (put the code in a .vbs file)
Set MyApp = CreateObject("QlikTech.QlikView")
Set MyDoc = MyApp.OpenDoc ("C:\QlikViewApps\Demo.qvw","","")
Set ActiveDocument = MyDoc
ActiveDocument.Reload
Set Button1 = ActiveDocument.GetSheetObject("BU01")
Button1.Press MyDoc.GetApplication.Quit
Set MyDoc = Nothing
Set MyApp = Nothing
Delete file
FUNCTION DeleteFile(rFile)
set oFile = createObject("Scripting.FileSystemObject")
currentStatus = oFile.FileExists(rFile)
if currentStatus = true than
oFile.DeleteFile(rFile)
end if
set oFile = Nothing
END FUNCTION
SUB CallExample
DeleteFile ("C:\MyFile.PDF")
END SUB
Get reports information
function countReports
set ri = ActiveDocument.GetDocReportInfo
countReports = ri.Count
end function
function getReportInfo
set ri = ActiveDocument.GetDocReportInfo
set r = ri.Item(i)
getReportInfo = r.Id & "," & r.Name & "," & r.PageCount & CHR(10)
end function
Send mail using Google Mail
SUB SendMail
Dim objEmail
Const cdoSendUsingPort = 2 ' Send the message using SMTP
Const cdoBasicAuth = 1 ' Clear-text authentication
Const cdoTimeout = 60 ' Timeout for SMTP in seconds
mailServer = "smtp.gmail.com"
SMTPport = 465
mailusername = "MyAccount@gmail.com"
mailpassword = "MyPassword"
mailto = "destination@company.com"
mailSubject = "Subject line"
mailBody = "This is the email body"
Set objEmail = CreateObject("CDO.Message")
Set objConf = objEmail.Configuration
Set objFlds = objConf.Fields
With objFlds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = mailServer
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = SMTPport
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = cdoTimeout
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasicAuth
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = mailusername
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = mailpassword
.Update
End With
objEmail.To = mailto
objEmail.From = mailusername
objEmail.Subject = mailSubject
objEmail.TextBody = mailBody
objEmail.AddAttachment "C:\report.pdf"
objEmail.Send
Set objFlds = Nothing
Set objConf = Nothing
Set objEmail = Nothing
END SUB
Changing Font setting of an Object
SUB Font()
set obj = ActiveDocument.GetSheetObject("BU01")
set fnt = obj.GetFrameDef.Font
fnt.PointSize1000 = fnt.PointSize1000 + 1000
fnt.FontName = "Calibri"
fnt.Bold = true
fnt.Italic = true
fnt.Underline = true
obj.SetFont fnt
END SUB
To Show and Hide Tab row
Sub ShowTab
rem Hides tabrow in document properties
set docprop = ActiveDocument.GetProperties
docprop.ShowTabRow=true
ActiveDocument.SetProperties docprop
End Sub
Sub HideTab
rem Hides tabrow in document properties
set docprop = ActiveDocument.GetProperties
docprop.ShowTabRow=false
ActiveDocument.SetProperties docprop
End Sub
Always One Selected Enable / Disable setting through Macro
Sub AlwaysOneSelected
set obj = ActiveDocument.GetSheetObject("LB02")
set boxfield=obj.GetField
set fprop = boxfield.GetProperties
fprop.OneAndOnlyOne = True
boxfield.SetProperties fprop
End Sub
Sub RemoveAlwaysOneSelected
set obj = ActiveDocument.GetSheetObject("LB02")
set boxfield=obj.GetField
set fprop = boxfield.GetProperties
fprop.OneAndOnlyOne = False
boxfield.SetProperties fprop
ActiveDocument.ClearAll True
End Sub
Reading Rows and Columns in a table object
Sub ReadStraightTable
Set Table = ActiveDocument.GetSheetObject( "CH01" )
For RowIter = 0 to table.GetRowCount-1
For ColIter = 0 to table.GetColumnCount-1
set cell = table.GetCell(RowIter,ColIter)
Msgbox(cell.Text)
Next
Next
End Sub
Get number of Rows in a Straight or Pivot tables
function ReadRowsCount
set v = ActiveDocument.GetVariable("variableName")
v.SetContent ActiveDocument.GetSheetObject( "CH01" ).GetRowCount-1, true
end function
Get and Set variable values in macros
function setVariable(name, value)
set v = ActiveDocument.GetVariable("variableName")
v.SetContent value,true
end function
function getVariable(name)
set v = ActiveDocument.GetVariable("variableName")
getVariable = v.GetContent.String
end function
Export chart data to QVD file, the chart may Bar/Line/StraightTable/Pivot etc.
sub ChartToQVD
set obj = ActiveDocument.GetSheetObject("CH01")
obj.ExportEx "QvdName.qvd", 4
end sub
Export Charts as image for each value selection in a Listbox
FUNCTION ExportObjectToJpg( ObjID, fName)
ctiveDocument.GetSheetObject(ObjID).ExportBitmapToFile fName
END FUNCTION
SUB ExportChartByListboxValues
DIM fname, value, filePath, timestamp
filePath = ActiveDocument.Variables("vPDFFlagPath").GetContent.STRING
timestamp = Year(Now()) & DatePart("m", Now()) & DatePart("d", Now()) & DatePart("h", Now()) & DatePart("n", Now()) & DatePart("s", Now())
SET Doc = ActiveDocument
fieldName = "EmployeeID"
SET Field = Doc.Fields(fieldName).GetPossibleValues
FOR index = 0 to Field.Count-1
Doc.Fields(fieldName).Clear
Doc.Fields(fieldName).SELECT
Field.Item(index).Text
fileName = Field.Item(index).Text & "_" & timestamp & ".jpg"'Field.Item(index).Text & DateValue
ExportObjectToJpg "CH420", filePath & fileName
NEXT
Doc.Fields(fieldName).Clear
END SUB
Checks whether given folder exists if not creates the given folder
Function CheckFolderExists(path)
Set fileSystemObject = CreateObject("Scripting.FileSystemObject")
If Not fileSystemObject.FolderExists(path) Than
fileSystemObject.CreateFolder(path)
End If
End Function
Minimize the chart object and move the chart position 20 pixels down and 15 right
Sub MoveChart
set mybox = ActiveDocument.GetSheetObject("CH09")
mybox.Minimize
set fr = mybox.GetFrameDef
pos = fr.MinimizedRect
pos.Top = pos.Top + 20
pos.Left = pos.Left + 15
mybox.SetFrameDef fr
end sub
Move Chart Object 20 pixels down and 15 right
Sub MoveChart
set obj = ActiveDocument.GetSheetObject("CH09")
pos = obj.GetRect
pos.Top = pos.Top + 20
pos.Left = pos.Left + 15
obj.SetRect pos
End Sub
Export Table charts Side by Side in a single Excel sheet
Function ExportCharts()
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = true
Set xlDoc = xlApp.Workbooks.Add 'open new workbook
nSheetsCount = 0
CALL RemoveDefaultSheet(xlDoc)
nSheetsCount = xlDoc.Sheets.Count
xlDoc.Sheets(nSheetsCount).Select
Set xlSheet = xlDoc.Sheets(nSheetsCount)
CALL ExportRevenueWidgets(xlDoc,xlSheet)
End Function
'Call Export Widgets By Sheet
Function ExportRevenueWidgets(xlDoc,xlSheet)
CALL Export(xlDoc,xlSheet,"CH09", "A")
CALL Export(xlDoc,xlSheet,"CH09", "D")
End Function
'Export Widgets
Function Export(xlDoc, xlSheet,widgetID, columnStart)
nRow = xlSheet.UsedRange.Rows.Count
nRow = 1
Set SheetObj = ActiveDocument.GetSheetObject(widgetID)
'Copy the chart object to clipboard
SheetObj.CopyTableToClipboard true
'Paste the chart object in Excel file
xlSheet.Paste xlSheet.Range(columnStart&nRow)
End Function
'Remove Default Sheets from Excel Files
Sub RemoveDefaultSheet(xlDoc)
Do
nSheetsCount = xlDoc.Sheets.Count
If nSheetsCount = 1 than
Exit Do
Else
xlDoc.Sheets(nSheetsCount).Select
xlDoc.ActiveSheet.Delete
End If
Loop
End Sub
Setting Scroll bar of a chart to Right side by default
SUB StartScrollRight
SET chartObject = ActiveDocument.GetSheetObject("CH01")
SET chartProperties = chartObject.GetProperties
chartProperties.ChartProperties.XScrollInitRight = true
chartObject.SetProperties chartProperties
END SUB
Show hide expression in Straight / Pivot table
Sub ShowHideExpression()
SET chartObj = ActiveDocument.GetSheetObject("CH01")
SET chartProp= chartObj.GetProperties
SET expr = chartProp.Expressions.Item(1).Item(0).Data.ExpressionData
expr.Enable = False // Hides First expression
SET expr = chartProp.Expressions.Item(2).Item(0).Data.ExpressionData
expr.Enable = True // Displays Second expression
End Sub
To reset InputField values
Sub ResetInputField
' Reset the InputField
set fld = ActiveDocument.Fields("InputFieldName")
fld.ResetInputFieldValues 0, 0 ' 0 = All
values reset, 1 = Reset Possible value, 2 = Reset single value
End Sub
To set InputField values
Sub SetInputField
set fld = ActiveDocument.Fields("Budget")
fld.SetInputFieldValue 0, "999" ' Sets InputField value to 999
End Sub
Clear specific Fields
SUB ClearFields
SET Doc = ActiveDocument
Doc.Fields(FieldName1).Clear
Doc.Fields(FieldName2).Clear
Doc.Fields(FieldName3).Clear
Doc.Fields(DateFieldNameN).Clear
END SUB
Export chart to CSV
SUB ExportChartToCSV
SET objChart = ActiveDocument.GetSheetObject("CH01")
objChart.Export "C:\Data.CSV", ", "
END SUB
Fit zoom to Window
Sub FitZoomToWindow
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.ActiveSheet.FItZoomToWindow
End Sub
Macro to get fast change chart type in a variable
10 - Pivot Table
11 - Straight Table
12 - Bar
15 - Line
Sub GetChartType()
set chart = ActiveDocument.getsheetobject("CH01")
set p = chart.GetProperties
set v = ActiveDocument.GetVariable("vFastChangeChartType")
v.SetContent
chart.GetObjectType,true
end sub
Open IE browser with URL based on a selected Dimension value - Use below macro in Document Properties Field Event Triggers
Create a variable
vSelectedURL : =Only([Image Location])
Sub Browse()
set v = ActiveDocument.GetVariable("vSelectedURL")
Set ie = CreateObject("Internetexplorer.Application")
ie.Visible = True
ie.Navigate v.GetContent.String
End Sub
Export multiple chart to Microsoft PowerPoint slides
Sub ExportPPT
Set objPPT = CreateObject("PowerPoint.Application")
objPPT.Visible = True
Set objPresentation = objPPT.Presentations.open("YourPath\ppt.pptx")'file Path
Set PPSlide =objPresentation.Slides.Add(1,12)
ActiveDocument.GetSheetObject("CH1").CopyBitmapToClipboard
PPSlide.Shapes.Paste
PPSlide.Shapes(PPSlide.Shapes.Count).Top = 150 'This sets the top location of the image
PPSlide.Shapes(PPSlide.Shapes.Count).Left = 15 'This sets the left location
PPSlide.Shapes(PPSlide.Shapes.Count).Width = 240
PPSlide.Shapes(PPSlide.Shapes.Count).Height = 250
Set PPSlide = objPresentation.Slides.Add(1,12)
ActiveDocument.GetSheetObject("CH2").CopyBitmapToClipboard
PPSlide.Shapes.Paste
PPSlide.Shapes(PPSlide.Shapes.Count).Top = 150 'This sets the top location of the image
PPSlide.Shapes(PPSlide.Shapes.Count).Left = 15 'This sets the left location
PPSlide.Shapes(PPSlide.Shapes.Count).Width = 100
PPSlide.Shapes(PPSlide.Shapes.Count).Height = 200
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing
End Sub
Copy to Clip Board -Bit Map Image
Sub CopyObject
ActiveDocument.GetSheetObject("CH01").CopyBitmapToClipboard
End sub
Append data to existing Excel file
Sub AppendDataToExcel
dim doc, xlApp, xlDoc, xlSheet, LastRow
Const xlUp = -4162
set doc = ActiveDocument
Set xlApp = CreateObject("Excel.Application")
Set xlDoc = xlApp.Workbooks.Open("C:\Data.xlsx") ' Change filepath
xlapp.Visible = true ' you can also set it to false so that process done in background
Set xlSheet = xlDoc.Worksheets("Sheet1") ' Replace Sheet1 with your sheet name
xlSheet.Activate
LastRow = xlSheet.Cells(xlSheet.Rows.Count, 1).End(xlUp).Row
msgbox LastRow
xlSheet.Cells(LastRow + 1, 1).Select
doc.GetSheetObject("TB01").CopyTableToClipboard true 'Replace TB01 with your chart object ID
xlSheet.Paste
xlDoc.Save
xlDoc.Close
xlApp.Quit
End Sub
Export all objects of a Container to Excel
sub Export
set oXL = CreateObject("Excel.Application")
oXL.DisplayAlerts = False
oXL.visible=True
Dim oXLDoc 'as Excel.Workbook
Dim i
Set oXLDoc = oXL.Workbooks.Add
'---------------------------------------
Set ContainerObj = ActiveDocument.GetSheetObject("CT02")
Set ContProp=ContainerObj.GetProperties
aSheetObj=Array("CH02","CH03","CH06")
'---------------------------------------
for i=0 to UBound(aSheetObj)
'ActiveDocument.GetApplication.WaitForIdle
oXL.Sheets.Add
oXL.ActiveSheet.Move ,oXL.Sheets( oXL.Sheets.Count )
ContProp.SingleObjectActiveIndex = i
ContainerObj.SetProperties ContProp
Set oSH = oXL.ActiveSheet
oSH.Range("A1").Select
Set obj = ActiveDocument.GetSheetObject(aSheetObj(i))
obj.CopyTableToClipboard True
oSH.Paste
sCaption=obj.GetCaption.Name.v
set obj=Nothing
oSH.Rows("1:1").Select
oXL.Selection.Font.Bold = True
oSH.Cells.Select
oXL.Selection.Columns.AutoFit
oSH.Range("A1").Select
oSH.Name=left(sCaption,30)
set oSH=Nothing
next
Call Excel_DeleteBlankSheets(oXLDoc)
oXL.DisplayAlerts = True
'// Finally select the first sheet
oXLDoc.Sheets(1).Select
'---------------------------------------
set oXL =Nothing
set oXLDoc =Nothing
end sub
Private Sub Excel_DeleteBlankSheets(ByRef oXLDoc)
For Each ws In oXLDoc.Worksheets
If (not HasOtherObjects(ws)) than
If oXLDoc.Application.WorksheetFunction.CountA(ws.Cells) = 0 Than
On Error Resume Next
Call ws.Delete()
End If
End If
Next
End Sub
Public Function HasOtherObjects(ByRef objSheet) 'As Boolean
Dim c
If (objSheet.ChartObjects.Count > 0) Than
HasOtherObjects = true
Exit function
End If
If (objSheet.Pictures.Count > 0) Than
HasOtherObjects = true
Exit function
End If
If (objSheet.Shapes.Count > 0) Than
HasOtherObjects = true
Exit function
End If
HasOtherObjects = false
End Function
Get list of bookmarks in Variable
Sub GetBookmarks
bookmarks = ActiveDocument.GetDocBookmarkNames
dim BM
for i = lbound(bookmarks) to ubound(bookmarks)
if(i=0) then
BM="'"&bookmarks(i)&"'"
else
BM=BM&",'"&bookmarks(i)&"'"
end if
next
set v = ActiveDocument.GetVariable("vVariable")
v.SetContent BM,true
End Sub
Export charts for each bookmark in separate excel sheets
Function ExportCharts()
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = true
Set xlDoc = xlApp.Workbooks.Add 'open new workbook
nSheetsCount = 0
CALL RemoveDefaultSheet(xlDoc)
bookmarks = ActiveDocument.GetDocBookmarkNames
for i = lbound(bookmarks) to ubound(bookmarks)
ActiveDocument.RecallDocBookmark bookmarks(i)
nSheetsCount = xlDoc.Sheets.Count
msgbox nSheetsCount
xlDoc.Sheets(nSheetsCount).Select
Set xlSheet = xlDoc.Sheets(nSheetsCount)
msgbox xlSheet.Name
CALL ExportRevenueWidgets(xlDoc,xlSheet)
' set nSheetsCount = nSheetsCount + 1
if i <> ubound(bookmarks) than
xlDoc.Sheets.Add
xlDoc.ActiveSheet.Move ,xlDoc.Sheets( xlDoc.Sheets.Count )
end if
' msgbox xlDoc.Sheets.Count
next
End Function
'Call Export Widgets By Sheet
Function ExportRevenueWidgets(xlDoc,xlSheet)
CALL Export(xlDoc,xlSheet,"CH01", "A")
CALL Export(xlDoc,xlSheet,"CH02", "D")
End Function
'Export Widgets
Function Export(xlDoc, xlSheet,widgetID, columnStart)
nRow = xlSheet.UsedRange.Rows.Count
nRow = 1
Set SheetObj = ActiveDocument.GetSheetObject(widgetID)
'Copy the chart object to clipboard
SheetObj.CopyTableToClipboard true
'Paste the chart object in Excel file
xlSheet.Paste xlSheet.Range(columnStart&nRow)
End Function
'Remove Default Sheets from Excel Files
Sub RemoveDefaultSheet(xlDoc)
Do
nSheetsCount = xlDoc.Sheets.Count
If nSheetsCount = 1 than
Exit Do
Else
xlDoc.Sheets(nSheetsCount).Select
xlDoc.ActiveSheet.Delete
End If
Loop
End Sub
FUNCTION RunExe(cmd)
CreateObject("WScript.Shell").Exec(cmd)
END FUNCTION
SUB CallExample
RunExe("c:\Program Files\Internet Explorer\iexplore.exe")
END SUB
Export object to Excel
FUNCTION ExcelExport(objID)
set obj = ActiveDocument.GetSheetObject( objID )
w = obj.GetColumnCount
if obj.GetRowCount>1001 then
h=1000
else
h=obj.GetRowCount
end if
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add
objExcel.Worksheets(1).select()
objExcel.Visible = True
set CellMatrix = obj.GetCells2(0,0,w,h)
column = 1
for cc=0 to w-1
objExcel.Cells(1,column).Value = CellMatrix(0)(cc).Text objExcel.Cells(1,column).EntireRow.Font.Bold = True
column = column +1
next c = 1
r =2
for RowIter=1 to h-1
for ColIter=0 to w-1
objExcel.Cells(r,c).Value = CellMatrix(RowIter)(ColIter).Text
c = c +1
next r = r+1
c = 1
next
END FUNCTION
SUB CallExample ExcelExport( "CH01" )
END SUB
Export object to JPG
FUNCTION ExportObjectToJpg( ObjID, fName)
ActiveDocument.GetSheetObject(ObjID).ExportBitmapToFile
fName
END FUNCTION
SUB CallExample
ExportObjectToJpg "CH01", "C:\CH01Image.jpg"
END SUB
Save and exit QlikView
SUB SaveAndQuit ActiveDocument.Save
ActiveDocument.GetApplication.Quit
END SUB
Clone Dimension Group
SUB DuplicateGroups
SourceGroup = InputBox("Enter Source Group Name")
CopiesNo = InputBox("How many copies?")
SourceGroupProperties = ActiveDocument.GetGroup(SourceGroup).GetProperties
FOR i = 1 TO CopiesNo
SET DestinationGroup = ActiveDocument.CreateGroup(SourceGroupProperties.Name & "_" & i)
SET DestinationGroupProperties = DestinationGroup.GetProperties
IF SourceGroupProperties.IsCyclic THEN
DestinationGroupProperties.IsCyclic = true
DestinationGroup.SetProperties
DestinationGroupProperties
ELSE
SourceGroupProperties.IsCyclic = true
DestinationGroupProperties.SetProperties
SourceGroupProperties
END IF
SET Fields = SourceGroupProperties.FieldDefs
FOR c = 0 TO Fields.Count-1
SET fld = Fields(c)
DestinationGroup.AddField
fld.name
NEXT
Application.waitforidle
NEXT
END SUB
Open document with selection of current month
SUB DocumentOpen
ActiveDocument.Sheets("Intro").Activate
ActiveDocument.ClearAll (true)
ActiveDocument.Fields("YearMonth").Select
ActiveDocument.Evaluate("Date(MonthStart(Today(), 0),'MMM-YYYY')")
END SUB
Read and Write variables
FUNCTION getVariable(varName)
set v = ActiveDocument.Variables(varName)
getVariable = v.GetContent.String
END FUNCTION
SUB setVariable(varName, varValue)
set v = ActiveDocument.Variables(varName)
v.SetContent varValue, true
END SUB
Open QlikView application, reload, press a button and close (put the code in a .vbs file)
Set MyApp = CreateObject("QlikTech.QlikView")
Set MyDoc = MyApp.OpenDoc ("C:\QlikViewApps\Demo.qvw","","")
Set ActiveDocument = MyDoc
ActiveDocument.Reload
Set Button1 = ActiveDocument.GetSheetObject("BU01")
Button1.Press MyDoc.GetApplication.Quit
Set MyDoc = Nothing
Set MyApp = Nothing
Delete file
FUNCTION DeleteFile(rFile)
set oFile = createObject("Scripting.FileSystemObject")
currentStatus = oFile.FileExists(rFile)
if currentStatus = true than
oFile.DeleteFile(rFile)
end if
set oFile = Nothing
END FUNCTION
SUB CallExample
DeleteFile ("C:\MyFile.PDF")
END SUB
Get reports information
function countReports
set ri = ActiveDocument.GetDocReportInfo
countReports = ri.Count
end function
function getReportInfo
set ri = ActiveDocument.GetDocReportInfo
set r = ri.Item(i)
getReportInfo = r.Id & "," & r.Name & "," & r.PageCount & CHR(10)
end function
Send mail using Google Mail
SUB SendMail
Dim objEmail
Const cdoSendUsingPort = 2 ' Send the message using SMTP
Const cdoBasicAuth = 1 ' Clear-text authentication
Const cdoTimeout = 60 ' Timeout for SMTP in seconds
mailServer = "smtp.gmail.com"
SMTPport = 465
mailusername = "MyAccount@gmail.com"
mailpassword = "MyPassword"
mailto = "destination@company.com"
mailSubject = "Subject line"
mailBody = "This is the email body"
Set objEmail = CreateObject("CDO.Message")
Set objConf = objEmail.Configuration
Set objFlds = objConf.Fields
With objFlds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = mailServer
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = SMTPport
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = cdoTimeout
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasicAuth
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = mailusername
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = mailpassword
.Update
End With
objEmail.To = mailto
objEmail.From = mailusername
objEmail.Subject = mailSubject
objEmail.TextBody = mailBody
objEmail.AddAttachment "C:\report.pdf"
objEmail.Send
Set objFlds = Nothing
Set objConf = Nothing
Set objEmail = Nothing
END SUB
Changing Font setting of an Object
SUB Font()
set obj = ActiveDocument.GetSheetObject("BU01")
set fnt = obj.GetFrameDef.Font
fnt.PointSize1000 = fnt.PointSize1000 + 1000
fnt.FontName = "Calibri"
fnt.Bold = true
fnt.Italic = true
fnt.Underline = true
obj.SetFont fnt
END SUB
To Show and Hide Tab row
Sub ShowTab
rem Hides tabrow in document properties
set docprop = ActiveDocument.GetProperties
docprop.ShowTabRow=true
ActiveDocument.SetProperties docprop
End Sub
Sub HideTab
rem Hides tabrow in document properties
set docprop = ActiveDocument.GetProperties
docprop.ShowTabRow=false
ActiveDocument.SetProperties docprop
End Sub
Always One Selected Enable / Disable setting through Macro
Sub AlwaysOneSelected
set obj = ActiveDocument.GetSheetObject("LB02")
set boxfield=obj.GetField
set fprop = boxfield.GetProperties
fprop.OneAndOnlyOne = True
boxfield.SetProperties fprop
End Sub
Sub RemoveAlwaysOneSelected
set obj = ActiveDocument.GetSheetObject("LB02")
set boxfield=obj.GetField
set fprop = boxfield.GetProperties
fprop.OneAndOnlyOne = False
boxfield.SetProperties fprop
ActiveDocument.ClearAll True
End Sub
Reading Rows and Columns in a table object
Sub ReadStraightTable
Set Table = ActiveDocument.GetSheetObject( "CH01" )
For RowIter = 0 to table.GetRowCount-1
For ColIter = 0 to table.GetColumnCount-1
set cell = table.GetCell(RowIter,ColIter)
Msgbox(cell.Text)
Next
Next
End Sub
Get number of Rows in a Straight or Pivot tables
function ReadRowsCount
set v = ActiveDocument.GetVariable("variableName")
v.SetContent ActiveDocument.GetSheetObject( "CH01" ).GetRowCount-1, true
end function
Get and Set variable values in macros
function setVariable(name, value)
set v = ActiveDocument.GetVariable("variableName")
v.SetContent value,true
end function
function getVariable(name)
set v = ActiveDocument.GetVariable("variableName")
getVariable = v.GetContent.String
end function
Export chart data to QVD file, the chart may Bar/Line/StraightTable/Pivot etc.
sub ChartToQVD
set obj = ActiveDocument.GetSheetObject("CH01")
obj.ExportEx "QvdName.qvd", 4
end sub
Export Charts as image for each value selection in a Listbox
FUNCTION ExportObjectToJpg( ObjID, fName)
ctiveDocument.GetSheetObject(ObjID).ExportBitmapToFile fName
END FUNCTION
SUB ExportChartByListboxValues
DIM fname, value, filePath, timestamp
filePath = ActiveDocument.Variables("vPDFFlagPath").GetContent.STRING
timestamp = Year(Now()) & DatePart("m", Now()) & DatePart("d", Now()) & DatePart("h", Now()) & DatePart("n", Now()) & DatePart("s", Now())
SET Doc = ActiveDocument
fieldName = "EmployeeID"
SET Field = Doc.Fields(fieldName).GetPossibleValues
FOR index = 0 to Field.Count-1
Doc.Fields(fieldName).Clear
Doc.Fields(fieldName).SELECT
Field.Item(index).Text
fileName = Field.Item(index).Text & "_" & timestamp & ".jpg"'Field.Item(index).Text & DateValue
ExportObjectToJpg "CH420", filePath & fileName
NEXT
Doc.Fields(fieldName).Clear
END SUB
Checks whether given folder exists if not creates the given folder
Function CheckFolderExists(path)
Set fileSystemObject = CreateObject("Scripting.FileSystemObject")
If Not fileSystemObject.FolderExists(path) Than
fileSystemObject.CreateFolder(path)
End If
End Function
Minimize the chart object and move the chart position 20 pixels down and 15 right
Sub MoveChart
set mybox = ActiveDocument.GetSheetObject("CH09")
mybox.Minimize
set fr = mybox.GetFrameDef
pos = fr.MinimizedRect
pos.Top = pos.Top + 20
pos.Left = pos.Left + 15
mybox.SetFrameDef fr
end sub
Move Chart Object 20 pixels down and 15 right
Sub MoveChart
set obj = ActiveDocument.GetSheetObject("CH09")
pos = obj.GetRect
pos.Top = pos.Top + 20
pos.Left = pos.Left + 15
obj.SetRect pos
End Sub
Export Table charts Side by Side in a single Excel sheet
Function ExportCharts()
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = true
Set xlDoc = xlApp.Workbooks.Add 'open new workbook
nSheetsCount = 0
CALL RemoveDefaultSheet(xlDoc)
nSheetsCount = xlDoc.Sheets.Count
xlDoc.Sheets(nSheetsCount).Select
Set xlSheet = xlDoc.Sheets(nSheetsCount)
CALL ExportRevenueWidgets(xlDoc,xlSheet)
End Function
'Call Export Widgets By Sheet
Function ExportRevenueWidgets(xlDoc,xlSheet)
CALL Export(xlDoc,xlSheet,"CH09", "A")
CALL Export(xlDoc,xlSheet,"CH09", "D")
End Function
'Export Widgets
Function Export(xlDoc, xlSheet,widgetID, columnStart)
nRow = xlSheet.UsedRange.Rows.Count
nRow = 1
Set SheetObj = ActiveDocument.GetSheetObject(widgetID)
'Copy the chart object to clipboard
SheetObj.CopyTableToClipboard true
'Paste the chart object in Excel file
xlSheet.Paste xlSheet.Range(columnStart&nRow)
End Function
'Remove Default Sheets from Excel Files
Sub RemoveDefaultSheet(xlDoc)
Do
nSheetsCount = xlDoc.Sheets.Count
If nSheetsCount = 1 than
Exit Do
Else
xlDoc.Sheets(nSheetsCount).Select
xlDoc.ActiveSheet.Delete
End If
Loop
End Sub
Setting Scroll bar of a chart to Right side by default
SUB StartScrollRight
SET chartObject = ActiveDocument.GetSheetObject("CH01")
SET chartProperties = chartObject.GetProperties
chartProperties.ChartProperties.XScrollInitRight = true
chartObject.SetProperties chartProperties
END SUB
Show hide expression in Straight / Pivot table
Sub ShowHideExpression()
SET chartObj = ActiveDocument.GetSheetObject("CH01")
SET chartProp= chartObj.GetProperties
SET expr = chartProp.Expressions.Item(1).Item(0).Data.ExpressionData
expr.Enable = False // Hides First expression
SET expr = chartProp.Expressions.Item(2).Item(0).Data.ExpressionData
expr.Enable = True // Displays Second expression
End Sub
To reset InputField values
Sub ResetInputField
' Reset the InputField
set fld = ActiveDocument.Fields("InputFieldName")
fld.ResetInputFieldValues 0, 0 ' 0 = All
values reset, 1 = Reset Possible value, 2 = Reset single value
End Sub
To set InputField values
Sub SetInputField
set fld = ActiveDocument.Fields("Budget")
fld.SetInputFieldValue 0, "999" ' Sets InputField value to 999
End Sub
Clear specific Fields
SUB ClearFields
SET Doc = ActiveDocument
Doc.Fields(FieldName1).Clear
Doc.Fields(FieldName2).Clear
Doc.Fields(FieldName3).Clear
Doc.Fields(DateFieldNameN).Clear
END SUB
Export chart to CSV
SUB ExportChartToCSV
SET objChart = ActiveDocument.GetSheetObject("CH01")
objChart.Export "C:\Data.CSV", ", "
END SUB
Fit zoom to Window
Sub FitZoomToWindow
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.ActiveSheet.FItZoomToWindow
End Sub
Macro to get fast change chart type in a variable
10 - Pivot Table
11 - Straight Table
12 - Bar
15 - Line
Sub GetChartType()
set chart = ActiveDocument.getsheetobject("CH01")
set p = chart.GetProperties
set v = ActiveDocument.GetVariable("vFastChangeChartType")
v.SetContent
chart.GetObjectType,true
end sub
Open IE browser with URL based on a selected Dimension value - Use below macro in Document Properties Field Event Triggers
Create a variable
vSelectedURL : =Only([Image Location])
Sub Browse()
set v = ActiveDocument.GetVariable("vSelectedURL")
Set ie = CreateObject("Internetexplorer.Application")
ie.Visible = True
ie.Navigate v.GetContent.String
End Sub
Export multiple chart to Microsoft PowerPoint slides
Sub ExportPPT
Set objPPT = CreateObject("PowerPoint.Application")
objPPT.Visible = True
Set objPresentation = objPPT.Presentations.open("YourPath\ppt.pptx")'file Path
Set PPSlide =objPresentation.Slides.Add(1,12)
ActiveDocument.GetSheetObject("CH1").CopyBitmapToClipboard
PPSlide.Shapes.Paste
PPSlide.Shapes(PPSlide.Shapes.Count).Top = 150 'This sets the top location of the image
PPSlide.Shapes(PPSlide.Shapes.Count).Left = 15 'This sets the left location
PPSlide.Shapes(PPSlide.Shapes.Count).Width = 240
PPSlide.Shapes(PPSlide.Shapes.Count).Height = 250
Set PPSlide = objPresentation.Slides.Add(1,12)
ActiveDocument.GetSheetObject("CH2").CopyBitmapToClipboard
PPSlide.Shapes.Paste
PPSlide.Shapes(PPSlide.Shapes.Count).Top = 150 'This sets the top location of the image
PPSlide.Shapes(PPSlide.Shapes.Count).Left = 15 'This sets the left location
PPSlide.Shapes(PPSlide.Shapes.Count).Width = 100
PPSlide.Shapes(PPSlide.Shapes.Count).Height = 200
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing
End Sub
Copy to Clip Board -Bit Map Image
Sub CopyObject
ActiveDocument.GetSheetObject("CH01").CopyBitmapToClipboard
End sub
Append data to existing Excel file
Sub AppendDataToExcel
dim doc, xlApp, xlDoc, xlSheet, LastRow
Const xlUp = -4162
set doc = ActiveDocument
Set xlApp = CreateObject("Excel.Application")
Set xlDoc = xlApp.Workbooks.Open("C:\Data.xlsx") ' Change filepath
xlapp.Visible = true ' you can also set it to false so that process done in background
Set xlSheet = xlDoc.Worksheets("Sheet1") ' Replace Sheet1 with your sheet name
xlSheet.Activate
LastRow = xlSheet.Cells(xlSheet.Rows.Count, 1).End(xlUp).Row
msgbox LastRow
xlSheet.Cells(LastRow + 1, 1).Select
doc.GetSheetObject("TB01").CopyTableToClipboard true 'Replace TB01 with your chart object ID
xlSheet.Paste
xlDoc.Save
xlDoc.Close
xlApp.Quit
End Sub
Export all objects of a Container to Excel
sub Export
set oXL = CreateObject("Excel.Application")
oXL.DisplayAlerts = False
oXL.visible=True
Dim oXLDoc 'as Excel.Workbook
Dim i
Set oXLDoc = oXL.Workbooks.Add
'---------------------------------------
Set ContainerObj = ActiveDocument.GetSheetObject("CT02")
Set ContProp=ContainerObj.GetProperties
aSheetObj=Array("CH02","CH03","CH06")
'---------------------------------------
for i=0 to UBound(aSheetObj)
'ActiveDocument.GetApplication.WaitForIdle
oXL.Sheets.Add
oXL.ActiveSheet.Move ,oXL.Sheets( oXL.Sheets.Count )
ContProp.SingleObjectActiveIndex = i
ContainerObj.SetProperties ContProp
Set oSH = oXL.ActiveSheet
oSH.Range("A1").Select
Set obj = ActiveDocument.GetSheetObject(aSheetObj(i))
obj.CopyTableToClipboard True
oSH.Paste
sCaption=obj.GetCaption.Name.v
set obj=Nothing
oSH.Rows("1:1").Select
oXL.Selection.Font.Bold = True
oSH.Cells.Select
oXL.Selection.Columns.AutoFit
oSH.Range("A1").Select
oSH.Name=left(sCaption,30)
set oSH=Nothing
next
Call Excel_DeleteBlankSheets(oXLDoc)
oXL.DisplayAlerts = True
'// Finally select the first sheet
oXLDoc.Sheets(1).Select
'---------------------------------------
set oXL =Nothing
set oXLDoc =Nothing
end sub
Private Sub Excel_DeleteBlankSheets(ByRef oXLDoc)
For Each ws In oXLDoc.Worksheets
If (not HasOtherObjects(ws)) than
If oXLDoc.Application.WorksheetFunction.CountA(ws.Cells) = 0 Than
On Error Resume Next
Call ws.Delete()
End If
End If
Next
End Sub
Public Function HasOtherObjects(ByRef objSheet) 'As Boolean
Dim c
If (objSheet.ChartObjects.Count > 0) Than
HasOtherObjects = true
Exit function
End If
If (objSheet.Pictures.Count > 0) Than
HasOtherObjects = true
Exit function
End If
If (objSheet.Shapes.Count > 0) Than
HasOtherObjects = true
Exit function
End If
HasOtherObjects = false
End Function
Get list of bookmarks in Variable
Sub GetBookmarks
bookmarks = ActiveDocument.GetDocBookmarkNames
dim BM
for i = lbound(bookmarks) to ubound(bookmarks)
if(i=0) then
BM="'"&bookmarks(i)&"'"
else
BM=BM&",'"&bookmarks(i)&"'"
end if
next
set v = ActiveDocument.GetVariable("vVariable")
v.SetContent BM,true
End Sub
Export charts for each bookmark in separate excel sheets
Function ExportCharts()
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = true
Set xlDoc = xlApp.Workbooks.Add 'open new workbook
nSheetsCount = 0
CALL RemoveDefaultSheet(xlDoc)
bookmarks = ActiveDocument.GetDocBookmarkNames
for i = lbound(bookmarks) to ubound(bookmarks)
ActiveDocument.RecallDocBookmark bookmarks(i)
nSheetsCount = xlDoc.Sheets.Count
msgbox nSheetsCount
xlDoc.Sheets(nSheetsCount).Select
Set xlSheet = xlDoc.Sheets(nSheetsCount)
msgbox xlSheet.Name
CALL ExportRevenueWidgets(xlDoc,xlSheet)
' set nSheetsCount = nSheetsCount + 1
if i <> ubound(bookmarks) than
xlDoc.Sheets.Add
xlDoc.ActiveSheet.Move ,xlDoc.Sheets( xlDoc.Sheets.Count )
end if
' msgbox xlDoc.Sheets.Count
next
End Function
'Call Export Widgets By Sheet
Function ExportRevenueWidgets(xlDoc,xlSheet)
CALL Export(xlDoc,xlSheet,"CH01", "A")
CALL Export(xlDoc,xlSheet,"CH02", "D")
End Function
'Export Widgets
Function Export(xlDoc, xlSheet,widgetID, columnStart)
nRow = xlSheet.UsedRange.Rows.Count
nRow = 1
Set SheetObj = ActiveDocument.GetSheetObject(widgetID)
'Copy the chart object to clipboard
SheetObj.CopyTableToClipboard true
'Paste the chart object in Excel file
xlSheet.Paste xlSheet.Range(columnStart&nRow)
End Function
'Remove Default Sheets from Excel Files
Sub RemoveDefaultSheet(xlDoc)
Do
nSheetsCount = xlDoc.Sheets.Count
If nSheetsCount = 1 than
Exit Do
Else
xlDoc.Sheets(nSheetsCount).Select
xlDoc.ActiveSheet.Delete
End If
Loop
End Sub
Reset Forgotten MySql root Password Under Windows
If you forgot root password for your MySQL database server running under Microsoft Windows, there is still a way to access your database and even reset your password using the command prompt.
Follow next steps:
1. Stop your MySQL server completely, start “services.msc” using Run window, and stop the service there.
2. Open your MS-DOS command prompt using “cmd” inside the Run window. Then go to your MySQL bin folder, such as C:\MySQL\bin.
3. Execute the following command in the command prompt:
mysqld.exe -u root --skip-grant-tables
4. Leave the current MS-DOS command prompt as it is, and open a new MS-DOS command prompt window.
5. Go to your MySQL bin folder again.
6. Enter “mysql” and press enter.
7. You should now have the MySQL command prompt working. Type “use mysql;” so that we switch to the “mysql” database.
8. Execute the following command to update the password:
UPDATE user SET Password = PASSWORD('your_new_passowrd') WHERE User = 'root';
However, you can now run almost any SQL command that you wish.
After you are finished close the first command prompt, and type “exit;” in the second command prompt.
You can now start the MySQL service.
How To Recover "SA" Password in Microsoft SQL Server
If you have access to the Windows Server
administrative password and Windows Authentication is enabled you can easily
reset the sa password using SSMS. Simply login into the host Windows Server as
Administrator then open SSMS and connect to the Database Engine using Windows
Authentication. You’ll see a dialog.
GO
NOTE: Sometimes SQL Server Configuration manager won't appear in search, either due to it ,it was installed recently ,so it was not indexed or any reason. So you can use following commands as per your installed SQL Version.
Next, start the SQL
Server Instance. Then open an elevated command prompt and enter sqlcmd. In the
sqlcmd windows enter a command like you see following to add your login to the
sysadmin group.
After connecting you can
either use SSMS or T-SQL to change the sa password. To use SSMS navigate to the
Security node and then expand Logins and right click the sa login to change the
Password properties.
Alternatively you can
select New Query from the SSMS menu to open Query Editor and then run the
following T-SQL query to reset the password for the sa login.
GO
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
GO
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'NewPassw0rd'
GO
GO
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'NewPassw0rd'
GO
That’s all pretty easy
but what if you don’t have access an account that isn’t included in the
sysadmin role? Fortunately, beginning with SQL Server members of the
Windows Administrators group can access SQL Server in single-user mode. This
allows you to add a login to the sysadmin role which you can then use to start
SQL Server with administrative rights. The account needs to be a member of the
local administrators group.
To start SQL Server in
single-user mode add the parameter -m at the command line. The easiest way to
do this is to use Configuration Manager. Stop the SQL Server Instance you want
to change. Right click the instance to open the Properties dialog and click the
Startup Parameters tab. Enter –m in the Startup parameters option.
SQLServerManager13.msc SQLServer2016SQLServerManager12.msc SQLServer2014SQLServerManager11.msc SQLServer2012SQLServerManager10.msc SQLServer2008
EXEC sp_addsrvrolemember
'CONTOSO\mikeo2', 'sysadmin';
GO
GO
This example adds the
account CONTOSO\mikeo2 to the SQL Server sysadmin role. Next, use Configuration
Manager to stop the SQL Server services and remove the -m from the Startup
Parameters. Then restart the SQL Server service. You should be able to login with
sysadmin rights using the account you added. Then you change the sa password
using one of the techniques presented earlier.
Excel Functions
Consolidation
=INDIRECT("'[SECOND_FILE.XLS]"&MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,256)&"'!$"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&ROW())
=INDIRECT("'[SECOND_FILE.XLS]"&MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,256)&"'!$"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&ROW())
Demo Package V4. – Microsoft Dynamics AX 2012 R2 CU7
If you are a fan of windows 8, there is a special section for Windows 8 Applications for Microsoft Dynamics AX 2012
| Windows 8 Applications for Microsoft Dynamics AX 2012 Demo Scripts and Installation Links | ||
| Application Name | Title | Download |
| Approvals | Microsoft Dynamics AX Approvals Demo Script | Approvals (docx) 2.21 MB
Windows Store Installation link:
Approvals application |
| Expenses | Microsoft Dynamics AX Expense Demo Script | Expenses (docx) 2.75 MB
Windows Store Installation link:
Expenses application |
| Timesheets | Microsoft Dynamics AX Timesheets Demo Script | Timesheets (docx) 3.35 MB
Windows Store Installation link:
Timesheets application |
| Microsoft Dynamics Business Analyzer | Microsoft Dynamics AX Business Analyzer demo script for Microsoft Dynamics AX | Demo script (pdf) 1.28 MB
Windows Store Installation link:
Microsoft Dynamics Business Analyzer |
Subscribe to:
Posts (Atom)