iLogic - Get file names from directory, and post them all to Excel problem

[mhoE3YPJ]

Good day,

I've been working a long time on a work around for some heavy manual drawing labor.

The last thing I really need from iLogic, is to post all filenames from a specified directory,

to excel cells.

Right now I can make my code post the first name in the directory, and change to the next empty row,

but I cant get it to post all names.

Dim di As New IO.DirectoryInfo("P:\KUNDER\" & pathMappe & "\" & sOrdreNr & "\")
Dim diar1 As IO.FileInfo() = di.GetFiles("*.dxf")
Dim files As List(Of String) = New List(Of String)

Worksheet = ("C:\Users\mho\Desktop\test123.xlsx")
Sheetno = ("Ark1")

GoExcel.Open(Worksheet, Sheetno)
RowStart = 2
RowEnd = 100
For count = RowStart To RowEnd
	If String.IsNullOrEmpty(GoExcel.CellValue("A" & count)) Then 
		i = i + 1
	End If
Next
row = RowEnd - i + 1

For Each fi As IO.FileInfo In diar1  
GoExcel.CellValue("A" & row) = fi.Name
Next
GoExcel.Save

This is my code. I use my Userparameters from my asm file, to find my directory, and I want to specify I only need .dxf files to be posted to excel.

I will use this with Lantek Expert, to run a processes which takes the files, and nest them for our fiber laser cutter, hence why all the file names specified is a must.

Any help would be greatly appriciated!

[TomaszDąbrowski · Accepted solution]

In my opinion you need two changes.

You should close excel file at the end of rule.

You should increase "row" variable after each managed file.

Dim di As New IO.DirectoryInfo("P:\KUNDER\" & pathMappe & "\" & sOrdreNr & "\")
Dim diar1 As IO.FileInfo() = di.GetFiles("*.dxf")
Dim files As List(Of String) = New List(Of String)

Worksheet = ("C:\Users\mho\Desktop\test123.xlsx")
Sheetno = ("Ark1")

GoExcel.Open(Worksheet, Sheetno)
RowStart = 2
RowEnd = 100
For count = RowStart To RowEnd
	If String.IsNullOrEmpty(GoExcel.CellValue("A" & count)) Then 
		i = i + 1
	End If
Next
row = RowEnd - i + 1

For Each fi As IO.FileInfo In diar1  
GoExcel.CellValue("A" & row) = fi.Name
row=row+1
Next
GoExcel.Save
GoExcel.Close

[Michael.Navara · Accepted solution]

You don't increment the "row" variable in this loop. Writes only the last file to the Excel.

        For Each fi As IO.FileInfo In diar1  
            GoExcel.CellValue("A" & row) = fi.Name
        Next

 Solves this your issue?

For Each fi As IO.FileInfo In diar1  
   GoExcel.CellValue("A" & row) = fi.Name
   row = row + 1
Next

[mhoE3YPJ]

Thanks for the help guys! It works perfectly now!
One step closer to full automatisation!

Thanks @Michael.Navara and @TomaszDąbrowski 

Have a great day you two!

[王承之pmhker]

is this full code ? i creat a excel file and rename the sheet to “Ark1“，then i run the rule but nothing is happen

[mhoE3YPJ]

This code will need some adjustment for it to work with your assembly files.

Dim di As New IO.DirectoryInfo("P:\KUNDER\" & pathMappe & "\" & sOrdreNr & "\")
Dim diar1 As IO.FileInfo() = di.GetFiles("*.dxf")
Dim files As List(Of String) = New List(Of String)

Dim aColumn As String = "A"
Dim cColumn As String = "C"
Dim Row As Integer = 2

Worksheet = ("C:\Users\mho\Desktop\test123.xlsx")
Sheetno = ("Ark1")

GoExcel.Open(Worksheet, Sheetno)

While Row < 99
GoExcel.CellValue(aColumn & Row) = "" 'Value
GoExcel.CellValue(cColumn & Row) = "" 'Value
Row = Row+1
End While

RowStart = 2
RowEnd = 100
For count = RowStart To RowEnd
	If String.IsNullOrEmpty(GoExcel.CellValue("A" & count)) Then 
		i = i + 1
	End If
Next
row = RowEnd - i + 1

For Each fi As IO.FileInfo In diar1  
GoExcel.CellValue("A" & row) = fi.Name
GoExcel.CellValue("C" & row) = fi.DirectoryName + "\" + fi.Name
row=row+1
Next
GoExcel.Save
GoExcel.Close

The text marked in read, need to match your userparameters and excel name.

I'm using my userparameters to find my folder for my dxf files:

Dim di As New IO.DirectoryInfo("P:\KUNDER\" & pathMappe & "\" & sOrdreNr & "\")

"P:\KUNDER\" is our server destination for customers, "pathMappe" is a list of folders I can change, and sOrdreNr is the Order folder. I always save my files with the order number. 

If you have a specific directory you want to use everytime for this rule, you should do this instead:

Dim di As New IO.DirectoryInfo("C:\Users\mho\Documents\Inventor\UserParameterTest\")

Hope it helps, I can maybe help troubleshoot some more if you post your path of DXF files, and your iLogic rule.

[王承之pmhker]

thanks，it's working，thankyou