Need Macro Help

Tue, 18 Sep 2012 18:02:52 GMT

Hi All,

I have a macro that copies a report to Excel and then sends it via email. However, I now need to add another piece to this macro. If there is text in Text Box TX01, I want to copy that text and paste it in to the last row of Column A in the Excel file and highlight it in yellow like this:

I have attached the example QVW. Can anyone help me add this to the macro? Your help is appreciated.

Re: Need Macro Help

Thu, 04 Oct 2012 16:22:04 GMT

Can anyone help me with this?

Re: Need Macro Help

Thu, 04 Oct 2012 20:47:28 GMT

I got it to work. I guess the key was in using a QlikView variable rather than try to use xlUp in Excel. If anyone is curious, here is the working code:

sub ExcelFile

strDate = CDate(Date)
strDay = DatePart("d", strDate)
strMonth = DatePart("m", strDate)
strYear = DatePart("yyyy", strDate)
If strDay < 10 Then
strDay = "0" & strDay
End If
If strMonth < 10 Then
strMonth = "0" & strMonth
End If
GetFormattedDate = strMonth & "-" & strDay & "-" & strYear

Path = "C:\temp\"
FileName = "Test_" & GetFormattedDate & ".xlsx"

set XLApp = CreateObject("Excel.Application")
XLApp.Visible = True
set XLDoc = XLApp.Workbooks.Add
ActiveDocument.GetSheetObject("TB03").CopyTableToClipboard true

XLDoc.Sheets(1).Paste()

XLDoc.Sheets(1).Columns("A:D").EntireColumn.AutoFit
XLDoc.Sheets(1).Columns("A:A").ColumnWidth = 15.57
XLDoc.Sheets(1).Columns("B:B").ColumnWidth = 12.43
XLDoc.Sheets(1).Columns("C:C").ColumnWidth = 15.29
XLDoc.Sheets(1).Columns("D:D").ColumnWidth = 15.57XLDoc.Sheets(1).Name = "Export"

Set v = ActiveDocument.Variables("vSelection")
Selection=v.GetContent.String
Set XLSheet = XLDoc.Sheets("Export")
ActiveDocument.GetSheetObject("TX34").CopyTextToClipboard

XLSheet.Range("A" & Selection).Select
XLSheet.Paste
XLDoc.SaveAs Path & FileName
XLApp.Quit

Set myApp = CreateObject ("Outlook.Application")
Set myMessage = myApp.CreateItem(olMailItem)
myMessage.BodyFormat = 3 'Outlook.OlBodyFormat.olFormatRichText

myMessage.To = InputBox("Enter email address in johnsmith@mail.com format","Email Address")
myMessage.Attachments.Add "C:\temp\" & FileName
myMessage.Subject = "Test File " & Date()

myMessage.Send
Set myMessage = Nothing
Set myApp = Nothing
Set myInspector = Nothing
Set myDoc = Nothing

end sub

topic Re: Need Macro Help in QlikView

Need Macro Help

Re: Need Macro Help

Re: Need Macro Help