Using access to run excel modules

  • Thread starter Thread starter jeremiah
  • Start date Start date
J

jeremiah

I have DeleteEmptyRows code that works well in Excel but need to run it from
Access. When I try to run from Access I get "Method or Data Member not
found" . I have tried to run my excel code from a shared file and can't get
past the "Runtime error 1004" so am now attempting to run the entire routine
from Access and get different errors. Any help or ideas would be appreciated.

I downloading and saving a csv file that needs to be manipulated before I
can import it into my existing Access table so that the data will import into
the correct columns.
 
I have DeleteEmptyRows code that works well in Excel but need to run it from
Access.  When I try to run from Access I get "Method or Data Member not
found" .  I have tried to run my excel code from a shared file and can't get
past the "Runtime error 1004" so am now attempting to run the entire routine
from Access and get different errors.  Any help or ideas would be appreciated.

I downloading and saving a csv file that needs to be manipulated before I
can import it into my existing Access table so that the data will import into
the correct columns.  
Click to expand...

post your code.
Did you create an instance of Excel, open your file, then try to run
the DeleteEmptyRows command inside that instance of Excel?
 
Yes, I did. I was getting a runtime error 1004 - macro DeleteEmpty cannot be
found. I have tried to run it from my personal.xls and moved it to a shared
folder and run it from there, and tried to run it from the same folder that
the csv file was saved in. Couldn't figure that out either so thought I
would try this route but I get method or data member not found. I can't save
the modules in the same file as the csv file because it gets replaced each
week with a new file so I need to be able to call it from somewhere else. I
am sure it is just how I am designating where to call the code but I haven't
been able to figure a way around it. I get open and close the file but the
Delete step will not run.

Sub sRunCARMa()
Dim objXL As Object, x
On Error Resume Next
Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = True
'Open the Workbook
.workbooks.Open "Q:\ScanSrs\Database\2009\Oper
Reports\PUMSSUMOPER.xls"
'Include CARMA in menu, run AutoOpen
'//Make sure personal.xls is open

x = Application.Run("DeleteEmptyCSV")
End With
objXL.ActiveWorkbook.Close
objXL.Quit
objXL.Application.Quit
Set objXL = Nothing
End Sub
 
It is quite easy to perform operations in Excel, and control the entire
process from Access. Make sure you set a reference to Excel, and then run
this code in an Access module:



Option Compare Database
Option Explicit ' Use this to make sure your variables are defined

' One way to be able to use these objects throughout the Module is to
Declare them here, and not in a Sub

Private objExcel As Excel.Application
Private xlWB As Excel.Workbook
Private xlWS As Excel.Worksheet

Sub Rep()
Dim strFile As String
strFile = "C:\Crosstab_Query.xls" 'whatever the filename is...

'Of course, this is just an example; put the actual path to your actual file
here…
' Opens Excel and makes it Visible

Set objExcel = New Excel.Application
objExcel.Visible = True

' Opens up a Workbook
Set xlWB = objExcel.Workbooks.Open(strFile)

' Sets the Workseet to the last active sheet - Better to use the commented
version and use the name of the sheet.

Set xlWS = xlWB.ActiveSheet

' Set xlWS = xlWB("Sheet1")

With xlWS ' You are now working with the Named file and the named worksheet
' Your Excel code begins here…you can even record a macro and make the
process super easy!!

End With

' Close and Cleanup

xlWB.SaveAs xlSaveFile
xlWB.Close

xlapp.Quit
Set xlapp = Nothing
End Sub

HTH,
Ryan---
 
Back
Top