Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have an excel file containing data for each fruit code; the sheet name is formated by fruit code. For example the sheet for the apple is 9678 and it contains sales information for each fruit.
I have a loop that look for all fruit codes and read de excel file taking as sheet the code fruit but i want to validate if the code existing in my loop exist in the excel file; it is to avoid error during the reading data. it happend when there's not data for a fruit code.
I just want to read all sheet name to compare with fruit code in the bucle.
Any one help me?
Thanks,
Already i solved; I just read de file using excel driver conector and it reads all file sheets.
Here you are the code: the file path is specified in driver conector using a variable
OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=Excel Files;DBQ=$(vArchivo);DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"]; | ||||||
// Read list of sheets | ||||||
Temp_Tables: | ||||||
sqltables; | ||||||
// Enumerate las hojas del archivo | ||||||
for iSheet = 0 to NoOfRows('Temp_Tables') - 1 | ||||||
let vSheetName = peek('TABLE_NAME', iSheet, 'Temp_Tables'); | ||||||
let vSheetName = replace(replace(vSheetName, '$', ''), chr(39), '') & '$'; // sqltables seems to add a random $ sign and single quotes |
If(len('$(vSheetName)') = 5) then | |||||||
Concatenate(Metas) | |||||||
LOAD Sociedad as ID_Sociedad, | |||||||
Org.Ventas as ID_OrgVta, | |||||||
Left(Vendedor,8) as ID_Vendedor_Cte, | |||||||
SubField(ApplyMap('MapAV', Sociedad&'000'&[Cod Cliente]),'|',1) as ID_SocOV_Key, | |||||||
SubField(ApplyMap('MapAV', Sociedad&'000'&[Cod Cliente]),'|',2) as ID_Cliente_Key, | |||||||
// | '000'&[Cod Cliente] as ID_Cliente, | ||||||
[Saldo USD], | |||||||
Sociedad&Factura as NoDocumento_Key, | |||||||
FINANZAS as SaldoFI, | |||||||
Fecha as FechaDocumento, | |||||||
Vence as FechaVencimiento, | |||||||
// | MakeDate(2016,01,01) as FechaAplicacion | ||||||
'$(vFile)' as FechaAplicacion, | |||||||
'$(vFile2)' as ArchivoOrigen | |||||||
//// Sociedad&Org.Ventas&'000'&[Cod Cliente] as ID_Cliente_Key, | |||||||
FROM [$(vArchivo)] | |||||||
(biff, embedded labels, table is [$(vSheetName)]); | |||||||
ENDIF | |||||||
next | |||||||
DROP TABLE Temp_Tables; |
Can you provide the sample data?
Julisy,
Follow below steps:
Step1:
Then press Alt F11, double click on any sheets in the left of the opened window and copy the below code as shown in the below picture.
Sub renameSheets()
Dim iSheetCount
For iSheetCount = 1 To Sheets.Count
Sheets(iSheetCount).Name = iSheetCount
Next iSheetCount
End Sub
Step2:
Then run by clicking the run button like below
Step3:
Save as the file in xlsx fomat.
Step4:
Then Open QVW, Press Cntrl E, go to the code marked in red as shown in the below picture and change the number as equal to the sheets available in the file "Fruit Sheet" after running the macro. Assuming the file contain 290 sheets.
Eg. for loop=1 to 290
Hope this will helps u!
Thanks,
AS
HI,
Try like this
Load Multiple excel sheets using For loop
// Generate Empty table
Data:
LOAD
'' AS Dummy
AutoGenerate(0);
FOR Each sheet in ('Apple', 'Banana', 'Etc')
Concatenate(Data)
LOAD *
FROM [FileName.xlsx]
(ooxml, embedded labels, table is [$(sheet)]);
NEXT
DROP FIELD Dummy;
HOpe this helps you.
Regards,
jagan.
Its a lot easier to help you if you provide sample data to illustrate your spreadsheet and your requirements. Do you want to dynamically load all the sheets in a spreadsheet, and the actual sheet names are not known ahead of time? And compare them with a list of names/codes? Where is this list coming from? Do you just need the sheet names, or do you want to load all the data? Do all the sheets contain the same set of columns? etc etc
I also have no idea what this might mean:
>>I just want to read all sheet name to compare with fruit code in the bucle.
please provide the same data so that we can help you out on the same.
the problem here is you are setting the fruit name as fixed way and i can't to do it thats.
I have a table fruit that fill as dinamic way and it is the problem. i need to confirm if the fruit code exists in the excel file before to read the information, because if it's doesn't exists, the application won't work correctly.
There is only one excel file containing one sheet per fruit and the sheet name is a code fruit.
I hope you understand.
If you try this code, it won't work correctly because when the application try to read a sheet with name Apple (for example) and it doesn't exists in excel file, will happend an error in the application
Hi,
You can read the Excel sheet names using below URL, adjust the code according to your requirement. It will read all the Sheets in the Excel file.
Load all Excel files and all sheets in a folder
Regards,
Jagan.
Thanks,
Already i solved; I just read de file using excel driver conector and it reads all file sheets.
Here you are the code: the file path is specified in driver conector using a variable
OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=Excel Files;DBQ=$(vArchivo);DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"]; | ||||||
// Read list of sheets | ||||||
Temp_Tables: | ||||||
sqltables; | ||||||
// Enumerate las hojas del archivo | ||||||
for iSheet = 0 to NoOfRows('Temp_Tables') - 1 | ||||||
let vSheetName = peek('TABLE_NAME', iSheet, 'Temp_Tables'); | ||||||
let vSheetName = replace(replace(vSheetName, '$', ''), chr(39), '') & '$'; // sqltables seems to add a random $ sign and single quotes |
If(len('$(vSheetName)') = 5) then | |||||||
Concatenate(Metas) | |||||||
LOAD Sociedad as ID_Sociedad, | |||||||
Org.Ventas as ID_OrgVta, | |||||||
Left(Vendedor,8) as ID_Vendedor_Cte, | |||||||
SubField(ApplyMap('MapAV', Sociedad&'000'&[Cod Cliente]),'|',1) as ID_SocOV_Key, | |||||||
SubField(ApplyMap('MapAV', Sociedad&'000'&[Cod Cliente]),'|',2) as ID_Cliente_Key, | |||||||
// | '000'&[Cod Cliente] as ID_Cliente, | ||||||
[Saldo USD], | |||||||
Sociedad&Factura as NoDocumento_Key, | |||||||
FINANZAS as SaldoFI, | |||||||
Fecha as FechaDocumento, | |||||||
Vence as FechaVencimiento, | |||||||
// | MakeDate(2016,01,01) as FechaAplicacion | ||||||
'$(vFile)' as FechaAplicacion, | |||||||
'$(vFile2)' as ArchivoOrigen | |||||||
//// Sociedad&Org.Ventas&'000'&[Cod Cliente] as ID_Cliente_Key, | |||||||
FROM [$(vArchivo)] | |||||||
(biff, embedded labels, table is [$(vSheetName)]); | |||||||
ENDIF | |||||||
next | |||||||
DROP TABLE Temp_Tables; |