Hi all

I'm struggling with a for each loop that should loop through a all subfolders in a given folder path and load content of excel files into the same table.

The problem is that everytime it iterates to a new file and starts the load it increments the tablename.

My code looks like this:

set vRoot = 'C:\QlikView Storage\Group88\Data\NewFileFomats\*';

for Each dir in DirList(vRoot)

for Each file in Filelist('$(dir)\*.xlsx')

tmp:
load
*
from
[$(file)\*.xlsx](ooxml, embedded labels, table is 'Import QW');

next;

Loadresult:
load * Resident tmp;

drop table tmp;

next;

As is there are 3 subfolders in the path C:\QlikView Storage\Group88\Data\NewFileFomats\* and it iterates through them all and loads the files but at the end I'm left with "temp-1" table, no Loadresult-table  and it only has data from one file.

I know it is possible to load all files from a folder into the same table using something like 

Tmp:

Load * from 

[C:\QlikView Storage\Group88\Data\NewFileFomats\Loewe\*.xlsx]
(ooxml, embedded labels, table is Import QW);

But how do implement this behaviour in the loop?

Thanks in advance for the help.

Best regards

Hi,

My code for export is:

sub Export_CrtneKode
	  Timestamp = Year(Date())&"-"&Month(Date())&"-"&Day(Date())
	'Create the Excel spreadsheet
	Set excelFile = CreateObject("Excel.Application")
	excelFile.Visible = false 'or false to run in background
  
    'Create the WorkBook
    Set curWorkBook = excelFile.WorkBooks.Add
    'Create the Sheet
    'first chart object
    Set curSheet = curWorkBook.WorkSheets(1)

	'paste the object
	Set i = ActiveDocument.GetSheetObject("CH02")
    i.CopyTableToClipboard true
	curSheet.Cells(1, 1).Select
	curSheet.Paste
	
	'to clear the clipboard
	curSheet.Cells(1, 100).Copy
	
	
	'define all cells format	
	curSheet.Columns("A").EntireColumn.NumberFormat = "0"
	curSheet.Columns(2).ColumnWidth = 50
	curSheet.Columns("B").EntireColumn.NumberFormat = "@"
	curSheet.Columns("C").EntireColumn.NumberFormat = "@"
	curSheet.Columns("D").EntireColumn.NumberFormat = "0"
	curSheet.Columns(5).ColumnWidth = 20
	curSheet.Columns("E").EntireColumn.NumberFormat = "@"
	curSheet.Columns("F").EntireColumn.NumberFormat = "@"
	curSheet.Columns("G").EntireColumn.NumberFormat = "@"
	curSheet.Columns("H").EntireColumn.NumberFormat = "@"
			
	excelFile.Visible = true
	'Save the file and quit excel
	excelFile.DisplayAlerts = False
	curWorkBook.SaveAs "S:\Porocanje QlikView\Izdelki Farmadent\ČRTNE KODE FD"&" "&timestamp&".xlsx"
	excelFile.DisplayAlerts = True
    curWorkBook.Close
    excelFile.Quit
    'Cleanup
    Set curWorkBook = nothing
    Set excelFile = nothing    

end sub

 Column E is EAN code formatted as text.  After export the cell is displayed e.g. 6,94952E+12 instead of 6949517000018.

Any idea how can I solve this?

TIA, Igor

Hi everyone,

I want to have rolling 12 months on Sum and Count measure.

I have seen many post related to this, but nothing worked well with my scenario-

here is my scenario -

I have field as =

This is my Datefield =Period("MMM-YYYY") 

Sum(sales)

Count({<Sale= {'External'} >} ExternalSale)

So now i need to have rolling 12 months(From this October 2019 to October 2018)  in the above sum and Count measure.

what is the best way ?

Thanks in advance! 

Buenas tardes,

Tengo una app conectada a una base de datos y tambien a un archivo de Excel. 

Necesito saber si hay posibilidad de recargar solamente el Excel y no toda la aplicacion. Es viable?

Gracias

Saludos