Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
asgardd2
Creator III
Creator III

Send to excel without using system clipboard (question about macros)

Hello!

     My macros (export to xls) in QliKView application usually works about 3 minutes.(export over 110 xls files with data)

All this time system clipboard is busy, and then i want copy/paste at this time another data(i work on server) it is very dangerous... to operated on macros data. This task i need run every 30 minutes.

I using template file(xlsm ,with  macros) and then copy the information from qlikview via macros.

Do you have any suggestion how to avoid using the system clipboard?

I tried to export by two methods(i put into question some of the code). But every method using system clipboard:

Code1(using copytoclipboard method)

...
Name = "Alcohol.xlsm"
Template = "D:\Template_macros.xlsm"
DirMain = "D:\Alcohol\"
ActiveDocument.Fields("PFM").Select PFM
DirPFM = DirMain & PFM & "\" & Name
Set App = CreateObject("Excel.Application")
Set oWB=App.Workbooks.Open(Template)
App.Visible = False
Set Gra02 = ActiveDocument.GetSheetObject("TB01")
App.ActiveSheet.Cells(5,11).Activate
Gra02.CopyTableToClipboard True
App.ActiveSheet.Paste

    App.ActiveSheet.Rows("5:5").Delete

    oWB.SaveAs DirPFM,52  

App.DisplayAlerts = False

    oWB.Close True

    App.Quit

Code2(using range in excel):

...

  Set App = CreateObject("Excel.Application")

  App.Visible = False

  Set oWB2=App.Workbooks.Open(TempData)

  iRow = App.ActiveSheet.UsedRange.Row + App.ActiveSheet.UsedRange.Rows.Count - 1

  Set range1 = App.WorkSheets(1).Range("A2:" & iRowText)

  Set oWB=App.Workbooks.Open(Template)

  Set range2 = App.WorkSheets(1).Range("K5")

  range1.Copy range2

  App.DisplayAlerts = False

  oWB.SaveAs DirPFM,52 '-4143

  oWB.Close True

  oWB2.Close True

  App.Quit


1 Solution

Accepted Solutions
Siva_Sankar
Master II
Master II

Anton,

Check out the attached file to export more records without copying to clipboard.

View solution in original post

2 Replies
Siva_Sankar
Master II
Master II

Anton,

Check out the attached file to export more records without copying to clipboard.

asgardd2
Creator III
Creator III
Author

     Thank you for your reply! This macros works great!