2 Replies Latest reply: Oct 4, 2012 4:47 PM by Rebecca Molstad RSS

    Need Macro Help

    Rebecca Molstad

      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:

       

      Capture.JPG

       

       

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

        • Re: Need Macro Help
          Rebecca Molstad

          Can anyone help me with this?

            • Re: Need Macro Help
              Rebecca Molstad

              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