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

asgardd2 — Tue, 09 Sep 2014 05:25:52 GMT

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

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

Siva_Sankar — Tue, 09 Sep 2014 06:54:26 GMT

Anton,

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

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

asgardd2 — Wed, 10 Sep 2014 04:23:42 GMT

Thank you for your reply! This macros works great!

topic Re: Send to excel without using system clipboard (question about macros) in QlikView

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

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

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