topic Re: Exporting into txt file after exporting into excel in QlikView

Exporting into txt file after exporting into excel

qlikviewforum — Sat, 06 Oct 2012 10:14:28 GMT

How to export a table into excel and then exporting the same from excel to text file using macro. Please help!

Thanks,

qvforum

Re: Exporting into txt file after exporting into excel

Gysbert_Wassenaar — Sat, 06 Oct 2012 14:29:08 GMT

Maybe something like this:

XLSFile = "test.xls"

TXTFile= "test.txt"

Set objExcelApp = CreateObject("Excel.Application")
Set objExcelDoc = objExcelApp.Workbooks.Add

set xlSheet = objExcelDoc.Sheets("Sheet1")

ActiveDocument.GetSheetObject("TB01").CopyTableToClipboard true

XLDoc.Sheets(j).Range("A" & 1).Select

XLDoc.Sheets(j).Paste

objExcelDoc.SaveAs XLSFile, 56 'save as xls

objExcelDoc.SaveAs TXTFile, -4158 'save as txt

objExcelDoc.Close

Re: Exporting into txt file after exporting into excel

qlikviewforum — Sun, 07 Oct 2012 14:17:44 GMT

Object required: 'XLDoc'

I tried to execute something like below but getting the above error. Could you please help me out one this?

Sub Test 
XLSFile = "test.xls"
TXTFile= "test.txt"
Set objExcelApp = CreateObject("Excel.Application")
Set objExcelDoc = objExcelApp.Workbooks.Add
set xlSheet = objExcelDoc.Sheets("Sheet1")
ActiveDocument.GetSheetObject("TB01").CopyTableToClipboard true
XLDoc.Sheets(j).Range("A" & 1).Select
XLDoc.Sheets(j).Paste
objExcelDoc.SaveAs XLSFile, 56                'save as xls
objExcelDoc.SaveAs TXTFile, -4158          'save as txt
objExcelDoc.Close 
End Sub

Also what I have noticed is that you are exporting one in xls and other in txt. But what I need is to export first in xls then from xls export it in txt. Hope it is clear now!

Thanks

qvforum

Re: Exporting into txt file after exporting into excel

Gysbert_Wassenaar — Mon, 08 Oct 2012 07:38:26 GMT

Oops, my bad. I should learn to copy-paste properly. XLDoc should be objExcelDoc.

Re: Exporting into txt file after exporting into excel

qlikviewforum — Mon, 08 Oct 2012 08:15:37 GMT

Sub Test XLSFile = "test.xls"
TXTFile= "test.txt"
Set objExcelApp = CreateObject("Excel.Application")
Set objExcelDoc = objExcelApp.Workbooks.Add
set xlSheet = objExcelDoc.Sheets("Sheet1")
ActiveDocument.GetSheetObject("TB01").CopyTableToClipboard true
objExcelDoc.Sheets(j).Range("A" & 1).Select
objExcelDoc.Sheets(j).Paste
objExcelDoc.SaveAs XLSFile, 56                'save as xls
objExcelDoc.SaveAs TXTFile, -4158          'save as txt
objExcelDoc.Close 
End Sub

After doing above changes getting the following error message

Subscript out of range

Any help please!

Re: Exporting into txt file after exporting into excel

Gysbert_Wassenaar — Mon, 08 Oct 2012 08:43:21 GMT

This works for me.

Change the locations of the files, the objectname you want to export (mine is TB01), and the name of the sheet if you're using the english version of excel. My dutch version needs Blad1 instead of Sheet1

Sub Test

XLSFile = "C:\Temp\test.xls"

TXTFile= "C:\Temp\test.txt"

Set objExcelApp = CreateObject("Excel.Application")

Set objExcelDoc = objExcelApp.Workbooks.Add

set xlSheet = objExcelDoc.Sheets("Sheet1")

ActiveDocument.GetSheetObject("TB01").CopyTableToClipboard true

xlSheet.Range("A1").Select

xlSheet.Paste

objExcelApp.DisplayAlerts = false

objExcelDoc.SaveAs XLSFile

objExcelDoc.SaveAs TXTFile, -4158

objExcelDoc.Close

objExcelApp.DisplayAlerts = true

End Sub

Re: Exporting into txt file after exporting into excel

qlikviewforum — Mon, 08 Oct 2012 08:53:12 GMT

It works but it is not giving the required output

Original data:

File	Location
Development	"C:\temp"

Output:

File Location

Development """C:\temp"""

It has added double double quotes at the start and end. This is what the problem I am facing.

Re: Exporting into txt file after exporting into excel

Gysbert_Wassenaar — Mon, 08 Oct 2012 10:05:37 GMT

Ah, well this won't solve that problem. But why don't you strip the quotes from the locations first, either on load or in the table you want to export?

Re: Exporting into txt file after exporting into excel

qlikviewforum — Mon, 08 Oct 2012 11:07:23 GMT

Due to some reasons I cannot remove the quotes! That's the problem here...

Re: Exporting into txt file after exporting into excel

Gysbert_Wassenaar — Mon, 08 Oct 2012 11:31:46 GMT

The quotes MUST be displayed in your qlikview application!? Wow. Oh, well. Create a chart then that you use only for export, not display (maybe hide it on a hidden sheet). In that chart you can remove the quotes from the location strings.

Re: Exporting into txt file after exporting into excel

rbecher — Tue, 09 Oct 2012 09:02:30 GMT

This is a bug of the STORE command. It triples all double qoutes. You should open a ticket.