Question Call excel macro within a seperate application

[artwork21]

Hello,

I'm looking to call an excel macro (excel version 07) within a separate visual studio application (visual studio express 2010). Basically, a user clicks a browse dialog button to select a xls or xlsm file which is then added to a listbox. Next, I would like the macro to run when another button is clicked. The macro looks at the first column in excel and moves the listed .tif images from one folder to another. I referenced the excel libraries, but seem to be getting hung up on a few code statements (''''ERROR see code below). I would like to run the macro within visual studio for any selected macro or add the macro somehow add the macro to the selected excel file and run it. Thanks for your help!

'Here is the button click procedure

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

filenames = lstFiles.SelectedItem
Dim oExcelApp As Object
'oExcelApp = filenames
' Create a reference to the currently running excel application
oExcelApp = GetObject(filenames)
' Make the Excel Application Visible.
oExcelApp.Visible = True
' Run the excel procedure/macro "MoveFiles"
oExcelApp.run("MoveFiles")
oExcelApp.quit()

End Sub

'Here is the excel macro

Private Function movefiles()
Dim strDirectory As String
Dim strDestFolder As String
Dim strExt As String

strDirectory = "C:\Temp\Logs"
strDestFolder = "C:\Temp\Logs\Temp2"
strExt = "xls"
Dim myfilesystemobject As Object
Dim myfiles As Object
Dim myfile As Object
Dim rng As Excel.Range
Dim oexcel As Excel.Workbook

rng = oexcel.ActiveSheet.Range("A1:A3000") 'set this to the range of your filtered list
myfilesystemobject = CreateObject("Scripting.FileSystemObject")
myfiles = myfilesystemobject.GetFolder(strDirectory).Files
For Each cell In rnd ''''''ERROR
For Each myfile In myfiles
If Not IsNull(cell.Value) Then
If myfile = strDirectory & "\" & cell.Value Then
'Stop
With myfile
.copy(strDestFolder & "\" & myfile.Name)
End With
Else
End If
End If
Next myfile
Next cell
End Function

 

[artwork21]

Update

This


view sourceprint?
1 For Each cell In rnd ''''''ERROR


should probably be

view sourceprint?1 For Each cell In rng

Thank you. I changed "rnd" to "rng", however "rng" is still underlined in red and the tip says, "Expression is of type 'Excel Range', which is not a collection type. This procedure works fine in excel but still does not work in my visual studio application.

Thanks

Range has a property called Cells that is a collection. Try that one.

view sourceprint?1 For Each cell In rng.Cells

Ok, I believe I'm getting closer to solving this error. After adding ".cells" to "rng.cells" it pointed to me that Not IsNull needed to be changed to dBNull. It is now giving me an error on my oexcel excel.worksheet variable, line below.

rng = oexcel.ActiveSheet.Range("A1:A3000") 'set this to the range of your filtered list

Here is the entire code again updated:

Private Function movefiles()
Dim strDirectory As String
Dim strDestFolder As String
Dim strExt As String

strDirectory = "C:\Temp\Logs"
strDestFolder = "C:\Temp\Logs\Temp2"
strExt = "xls"
Dim myfilesystemobject As Object
Dim myfiles As Object
Dim myfile As Object
Dim rng As excel.Range
Dim oexcel As excel.Worksheet

rng = oexcel.ActiveSheet.Range("A1:A3000") 'set this to the range of your filtered list
myfilesystemobject = CreateObject("Scripting.FileSystemObject")
myfiles = myfilesystemobject.GetFolder(strDirectory).Files
For Each cell In rng.Cells
For Each myfile In myfiles
'If Not IsNull(cell.Value) Then
If Not (TypeOf cell.value(rng) Is DBNull) Then
If myfile = strDirectory & "\" & cell.Value Then
'Stop
With myfile
.copy(strDestFolder & "\" & myfile.Name)
End With
Else
End If
End If
Next myfile
Next cell
End Function