Exporting into txt file after exporting into excel

[qlikviewforum]

Hi

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

Thanks,

qvforum

[Gysbert_Wassenaar]

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

[qlikviewforum]

Hi

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

[Gysbert_Wassenaar]

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

[qlikviewforum]

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!

[Gysbert_Wassenaar]

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

[qlikviewforum]

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.

[Gysbert_Wassenaar]

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?

[qlikviewforum]

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

[Gysbert_Wassenaar]

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.