Export macro not working in client's machine

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

We have a macro which exports an object into excel and then emails it.

We do so by triggering excel and outlook.

The macro works fine on our local, Virtual machine and also from the server.

But in the client's machine it is throwing the following error -

The error is ActiveX component cant create object: 'Excel.Application'. Attaching the screen shot of the error.

Following is the macro -

Sub Super

'Defining all the variables

Dim fso1

Dim tempFolder

Dim OutApp

Dim OutMail

Dim Source

Dim fso

Dim text

Dim T

Dim Range

DIm OSUser

Dim RowCount

Set wshShell = CreateObject( "WScript.Shell" )

OSUser = wshShell.ExpandEnvironmentStrings( "%COMPUTERNAME%" )

'This defines the location of the Temp Folder

Set fso1 = CreateObject("Scripting.FileSystemObject")

tempFolder = fso1.GetSpecialFolder(2)

'Checking and deleting the previously created file of the same name

Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists(tempFolder & "\testmail_" & OSUser & ".txt") THEN fso.DeleteFile (tempFolder & "\testmail_" & OSUser & ".txt"), True End IF

If fso.FileExists(tempFolder & "\Sales_Report_" & OSUser & ".xlsx") THEN fso.DeleteFile (tempFolder & "\Sales_Report_" & OSUser & ".xlsx"), True End If

'Exporting the chart object which contains Email ID to text file

set sObject1 = ActiveDocument.GetSheetObject("CH52")

sObject1.Export (tempFolder & "\testmail_" & OSUser & ".txt"), ", "

'This triggers Excel Application

set oXL=CreateObject("Excel.Application")

set oXLDoc = oXL.Workbooks.Add() 'Adding a new Workbook

set oXLSheet2 = oXLDoc.Worksheets.Add 'Adding a new worksheet

oXLSheet2.Name = "Help" 'Naming this worksheet

oXLSheet2.Range("A1").Value = "RISKS" 'Naming this particular cell

oXLSheet2.Range("B1").Value = "DEFINITIONS"

oXLSheet2.Range("A2").Value = "Below AUP"

oXLSheet2.Range("A3").Value = "Below Avg Utilization"

oXLSheet2.Range("A4").Value = "Number of Big 4 assays"

oXLSheet2.Range("A5").Value = "RAP Customer?"

oXLSheet2.Range("B2").Value = "Average Unit Price at a Customer level < Average Unit Price at a Sub Region level"

oXLSheet2.Range("B3").Value = "ID Sales/Number of active M2000 SP Instruments at a customer level < ID Sales/Number of active M2000 SP Instruments at a Sub Region level"

oXLSheet2.Range("B4").Value = "Count of the Big 4 Assays - HIV Quant Reagent, HCV Quant Reagent, HBV Quant Reagent, CT/NG Reagent"

oXLSheet2.Range("B5").Value = "If the customer has active M2000 SP Instrument on RAP"

oXLSheet2.Columns.AutoFit 'Autofitting all the columns in this sheet

oXLSheet2.Range("A1:B1").Interior.Color = RGB(255,192,0) 'Giving colour to this range of cells

oXLSheet2.Range("A1:B1").Font.Bold = True 'Setting the font as Bold to this range of cells

oXLSheet2.Range("A1:B1").HorizontalAlignment = -4108 'Horizontal alignment as center of this range of cells

oXLSheet2.Range("A1:B5").Borders.LineStyle = 1 'Linestyle as '1' gives a continuous form of border

oXLSheet2.Range("A1:B5").Borders.Color = RGB(0,0,0) 'Colour for the border

oXLSheet2.Range("A1:B5").Borders.Weight = 2 'Thickness of the border

set oXLSheet = oXLDoc.Worksheets.Add 'Adding a new worksheet

oXLSheet.Name = "Sales Report" 'Naming this worksheet

oXLSheet.Range("A3").Select

Set obj = ActiveDocument.GetSheetObject("CH44")

oXLSheet.Range("A1").Value = "SALES REPORT"

oXLSheet.Range("A1").Font.Size = 12

oXLSheet.Range("A1:Q1").Merge

oXLSheet.Range("A1").HorizontalAlignment = -4108 'Horizontal alignment as center of this range of cells

oXLSheet.Range("A1").Font.Bold = True

oXLSheet.Range("A1").Interior.Color = RGB(102,102,255)

oXLSheet.Range("A2:M2").Interior.Color = RGB(255,192,0)

oXLSheet.Range("A2:F2").Merge

oXLSheet.Range("A2").Value = "CUSTOMER AND FRANCHISE"

oXLSheet.Range("A2").HorizontalAlignment = -4108

oXLSheet.Range("G2:I2").Merge

oXLSheet.Range("G2").Value = "SALES"

oXLSheet.Range("G2").HorizontalAlignment = -4108

oXLSheet.Range("J2:L2").Merge

oXLSheet.Range("J2").Value = "GROSS MARGIN"

oXLSheet.Range("J2").HorizontalAlignment = -4108

oXLSheet.Range("M2:Q2").Merge

oXLSheet.Range("M2").Value = "RISKS"

oXLSheet.Range("M2").HorizontalAlignment = -4108

obj.CopyTableToClipboard True

oXLSheet.Paste 'Exporting the chart CH44

sCaption=obj.GetCaption.Name.v

set obj=Nothing

oXLSheet.Rows("2:3").Select

oXLSheet.Range("A3:Q3").Font.Bold = True

oXLSheet.Cells.Select

oXLSheet.Columns.AutoFit

oXLSheet.Range("A3").Select

oXLSheet.Name=left(sCaption,30)

'Making the table border dynamic depending on number of rows

RowCount = oXLSheet.Range("A1").CurrentRegion.Rows.Count

oXLSheet.Range("A1:Q" & RowCount).Borders.LineStyle = 1

oXLSheet.Range("A1:Q" & RowCount).Borders.Color = RGB(0,0,0)

oXLSheet.Range("A1:Q" & RowCount).Borders.Weight = 2

'Exporting the table which contains Sub Region GM values v/s Sub Regions

oXLSheet.Range("S2").Select

Set obj = ActiveDocument.GetSheetObject("CH53")

obj.CopyTableToClipboard True

oXLSheet.Paste

oXLSheet.Range("S2").Value = "SUB REGION NAME"

oXLSheet.Range("T2").Value = "GROSS MARGIN"

oXLSheet.Columns.AutoFit

RowCount = oXLSheet.Range("S2").CurrentRegion.Rows.Count

oXLSheet.Range("S2:T" & RowCount+1).Borders.LineStyle = 1

oXLSheet.Range("S2:T" & RowCount+1).Borders.Color = RGB(0,0,0)

oXLSheet.Range("S2:T" & RowCount+1).Borders.Weight = 2

oXLSheet.Range("S2:T2").Interior.Color = RGB(255,192,0)

oXLSheet.Range("V2").Select

Set obj = ActiveDocument.GetSheetObject("CH55")

obj.CopyTableToClipboard True

oXLSheet.Paste

oXLSheet.Range("V2").Value = "REGION NAME"

oXLSheet.Range("W2").Value = "GROSS MARGIN"

oXLSheet.Columns.AutoFit

RowCount = oXLSheet.Range("V2").CurrentRegion.Rows.Count

oXLSheet.Range("V2:W" & RowCount+1).Borders.LineStyle = 1

oXLSheet.Range("V2:W" & RowCount+1).Borders.Color = RGB(0,0,0)

oXLSheet.Range("V2:W" & RowCount+1).Borders.Weight = 2

oXLSheet.Range("V2:W2").Interior.Color = RGB(255,192,0)

oXLSheet.Range("Y2").Select

Set obj = ActiveDocument.GetSheetObject("CH56")

obj.CopyTableToClipboard True

oXLSheet.Paste

oXLSheet.Range("Y2").Value = "AREA NAME"

oXLSheet.Range("Z2").Value = "GROSS MARGIN"

oXLSheet.Columns.AutoFit

RowCount = oXLSheet.Range("Y2").CurrentRegion.Rows.Count

oXLSheet.Range("Y2:Z" & RowCount+1).Borders.LineStyle = 1

oXLSheet.Range("Y2:Z" & RowCount+1).Borders.Color = RGB(0,0,0)

oXLSheet.Range("Y2:Z" & RowCount+1).Borders.Weight = 2

oXLSheet.Range("Y2:Z2").Interior.Color = RGB(255,192,0)

'This would make sure that the excel sheet does not pop up visible after changes

oXLDoc.Sheets("Sheet1").Visible = False

oXLSheet.SaveAs (tempFolder & "\Sales_Report_" & OSUser & ".xlsx") 'SaveAs the document

oXL.Application.Quit 'Close the document

set oXLDoc = Nothing

set oXL = Nothing

set oXLSheet = Nothing

set oXLSheet2 = Nothing

msgText = "your report has been exported"

Msgbox "Hello " & msgText

Set objExcel = CreateObject("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open(tempFolder & "\Sales_Report_" & OSUser & ".xlsx")

objExcel.Application.Visible = True

set objExcel =Nothing

ActiveDocument.Variables("vEmail_t").SetContent 0,True

End Sub

topic Export macro not working in client's machine in QlikView

Export macro not working in client's machine

Re: Export macro not working in client's machine