Running Excel macro from Access (1 Viewer)

Hi all

I am using Access to open a csv file and then populate a table with the data. This works fine with no problems.

However, in order to do this efficiently I have had to modify the csv file to remove the last 5 lines that the crm package has added to the file.

I have a excel macro that will remove the last five lines once it has found the word it is looking for. However if the macro doesn't find the word i need it to stop and close the file.

Here is the excel macro im using.

Function LastRow()


If WorksheetFunction.CountA(Cells) > 0 Then
LastRow = Cells.Find(What:="some name etc", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

End If
End Function

Sub DeleteRows()

Rows(LastRow - 4 & ":" & LastRow).Select
Selection.Delete Shift:=xlUp
Range("A1").Select


End Sub

Click to expand...

Follow the solution at this site.
http://www.access-programmers.co.uk/forums/showthread.php?t=242870
Note the Public function
A function will need a return value. If it is really a Sub, why make it a function? If it is actually a function, it is advised to define the return type ( e.g. Public Function MyDemo () as Integer)

If this works, got to Thread Tools and mark this as SOLVED

Also specify the macro for Excel to run as well in the case of running mulitiple macros. Some code will force opening a new instance of Excel each time Access code runs. A loop ends up with "?" amount of Excels open. If the intention is only launch Excel once, then it may not be a big deal, otherwise, it is advised to check and see if Excel is already open. If Excel is already open, avoid creating new Excel instances.

quick example

Rx

Many thanks for your reply. I have been away from the pc more or less since.However, I managed to get it working in a very similar way as you have described.

Here is my code

Sub RunExcelMacro()
Dim xl As Object

'Step 1: Start Excel, then open the target workbook as well as the personal.xlsb
'which holds the macro
Set xl = CreateObject("Excel.Application")
xl.workbooks.Open ("C:\Users\ian.elmes\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB")
xl.workbooks.Open ([textbox])

'Step 2: Make Excel invisible
xl.Visible = False

'Step 3: Run the target macro
xl.Run "PERSONAL.XLSB!deletelast5"

'Step 4: Close and save the workbooks, then close Excel
xl.ActiveWorkbook.Close (True)
xl.Quit ' closes PERSONAL.XLSB
xl.Quit ' closes the downloaded workbook


'Step 5: Memory Clean up.
Set xl = Nothing

End Sub

Click to expand...