Module crisis (Access 2000)

Topic

New Reply

Hello u guys!
I have a macro, which exports data from a query to a spreadsheet, now what I want to do is write a module, which would run this macro and open the spreadsheet that contains the exported data. I have no idea when it comes to visual basic but I did my best and came up with this macro but surely it has some errors and I cannot make it work so would somebody help me?

Sub BCP ()
Dim stDocName As String
Dim objExcel As Object

stDocName = “Export2Excell”
DoCmd.RunMacro stDocName

Set objExcel = CreateObject(“Excel.xls”)
objExcel.AppShow
‘objExcel.AppMaximize “”, 1 (optional)
objExcel.FileOpen “S:SRI_WO~1NEWFOL~1Trades~1.doc”
End Sub

Reply | Quote

Replies

Two options to get over the initial hump:

(1) Add a reference (in your module in the editor, Tools | References) to the Microsoft Excel object library. This will make your programming much easier, since you will get the Intellisense prompts for available properties and methods.

Then you would change your object to something like:

Dim objExcel as New Excel.Application

(2) Keep your code as is, but change the following line:

Set objExcel = CreateObject(“Excel.Application”)

(I haven’t tested beyond that, but maybe that’s all you need)

Reply | Quote

You might get some insight into the automation process from the Microsoft download that contains samples and white papers on automating Office apps for both Office 97 and 2000. Look for it at http://download.microsoft.com/download/off…US/OFFAUTMN.EXE It will at least give you an idea of the range of possibilities for automation between apps.

Reply | Quote

Thank you for the replies.
I’ve changed the codes the way you told me to, but it still is giving me error messages where I’ve put (#) Hash, See whats wrong with that please.,

Sub BCP()
Dim stDocName As String
Dim objExcel As New Excel.Application

stDocName = “Export2Excell”
DoCmd.RunMacro stDocName

Set objExcel = CreateObject(“Excel.Application”)
#objExcel.Application
‘objExcel.AppMaximize “”, 1 (optional)
#objExcel.FileOpen “S:SRI_WO~1NEWFOL~1Trades~1.doc”
End Sub

Reply | Quote

Hi, Princess. I have had a lot of luck using a macro to do everything it appears you need to do.

I create a macro that uses the TransferSpreadsheet Action and the RunApp Action. In the list of Action Arguements section of the build macro dialog box, there are six boxes that you need to fill in. The only box that is not clear on what needs to be entered is the File Name box. This is an example of what I put in this box: s:IGCNTracking(DataEntry)NewStatus.xls. This tells the TransferSpreadsheet the Excel file to transfer to.

In the RunApp action, there is a Command Line box. This one is a little tricky also. You have to have the path of the Excel executable program and the file name (the same as the File Name in the TransferSpreadsheet Action). This is an example of this box’s contents: c:Program FilesMicrosoft OfficeOfficeexcel.exe s:IGCNTracking(DataEntry)NewStatus.xls. Be sure there is a space between excel.exe and s:.

I think if you try this method, you will like it much better than trying to do this with VBA.

Reply | Quote

Hiya Lonnie, I tried RunApp, it gives me an error message for some reason, saying that the path is incorrect. I know for sure that the path is correct coz Thats the Path am using to transfer data, when it could find the document to tranfer data, why can’t it find the path to open the document? If I could only ask this question to a computer that could talk…

Reply | Quote

Thanks y’all, finally I got it to work the way Lonnie told me to do.
Thank you so much and Charlotte, thanks for the document you reccommended, I sure will use it to try to learn.

Reply | Quote

Good for you, Princess. What did you do to make it work?

Reply | Quote

Did you set a reference to the Excel object library? Otherwise, you don’t have access to the objects specific to that library, and that’s what this code requires.

Reply | Quote

Thanks guys, I used Lonnies idea, it didn’t work at first but when I called the folder by the dos names it worked. Thanks charlotte, but I didn’t use ur idea coz I have no idea what i was doing in visual basic so I thought I’d better do something that I can do.

Thanks guys, I wouldn’t have done it without ur help.

Reply | Quote