Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Module crisis (Access 2000)

    Hello u guys! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    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
    <img src=/S/help.gif border=0 alt=help width=23 height=15>

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Module crisis (Access 2000)

    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)

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Automation Samples

    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 <A target="_blank" HREF=http://download.microsoft.com/download/office2000dev/sample/2/WIN98/EN-US/OFFAUTMN.EXE>http://download.microsoft.com/download/off...US/OFFAUTMN.EXE</A> It will at least give you an idea of the range of possibilities for automation between apps.
    Charlotte

  4. #4
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation Samples

    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

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Indiana, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation Samples

    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.

  6. #6
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation Samples

    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...

  7. #7
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation Samples

    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.
    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Indiana, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation Samples

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

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automation Samples

    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.
    Charlotte

  10. #10
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation Samples

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •