Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Output report to HTML and run batch file? (2003)

    Hi forum members,

    Ok I know how to output a report to a file: DoCmd.OutputTo acReport, stDocName, acFormatHTML
    ----
    First Problem: I need to have relative paths. Each user of the database can have the database in a different folder, meaning that I want to output the file into "/online/file.html". The database would be in the root folder.
    -----

    Second Problem: I want to run a batch file after that, which uploads all the files to a FTP server. I have the batch file, but don't know how to run it from access.

    Hope you guys can help me out.

    -----------

    Oh just had a new problem, totally different:
    I have a filter function for all my forms so that only records from the current year are shown:

    Private Sub Form_Load()
    Me.Filter = "Year = " & Forms![frmYear]![cboreg]
    Me.FilterOn = True
    End Sub


    So far so good, but how can I now open that form in "Add-Mode" or in code acAdd? Of course this can't work the usual way because the Filter "overrules" that Add-Command and I'm back to the regular filtered output.
    This ain't a big problem, since I can still click on New Record whilst being in the actual form, I still would like the idea of the user making his choice before opening the form.

    Sincerely
    Dennis

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Output report to HTML and run batch file? (2003)

    Problem 1: CurrentProject.Path returns the path of the database.

    Problem 2: Shell "C:TestUpload.bat"

    Problem 3: Put the condition as criteria in the record source of the form, instead of using the On Load event.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output report to HTML and run batch file? (200

    thanks, works

    Problem 2: Is there some regulations or something to arguments? I'm trying to run a ftp-program which needs these arguments:

    -u myuser -p mypass -v 62.116.49.180 /test files/*

    So running it from the commandprompt looks like this:
    C:ncftp -u myuser -p mypass -v 62.116.49.180 /test files/*
    and it works.

    Now my command in access looks like this:
    Shell CurrentProject.Path & "onlineexportncftp -u myuser -p mypass -v 62.116.49.180 /test files/*", vbNormalFocus

    The program runs, but somehow it's not passing all arguments, because the program does nothing - waits a bit and then closes. So I guess access is somehow forgetting some variables but I don't know which.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Output report to HTML and run batch file? (200

    As a test, create a batch file that contains the complete instruction, and call this batch file using Shell.

  5. #5
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output report to HTML and run batch file? (200

    File test.bat:
    @echo off
    ncftp -u myuser -p mypass -v 62.116.49.180 /test files/*

    works like a charm. Same code in Access: program opening but wrong parameters.
    Access Code:
    Shell CurrentProject.Path & "ncftp -u myuser -p mypass -v -d test.txt 62.116.49.180 /test files/*", vbNormalFocus

    Does Access have it's own time-out for executing files? Maybe Access closes the file too early before it can even log in to the FTP server, even tho I doubt that... strange behaviour [img]/forums/images/smilies/sad.gif[/img]

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Output report to HTML and run batch file? (200

    Have you tried calling

    Shell "Test.bat"

  7. #7
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output report to HTML and run batch file? (200

    ok getting closer to somewhere.

    My Access code:
    DoCmd.OutputTo acReport, stDocName, acFormatHTML, CurrentProject.Path & "oexportfilesonlinedelegates.html"
    Shell CurrentProject.Path & "oexportupload.bat", vbNormalFocus
    The file upload.bat get's called up, but what is that???

    The file get's started in My Documents as working dir?!? It's a batch file so there's no option I can restrict the working dir to the actual folder and double clicking it IN the folder does the right commands.

    So instead of running from the folder
    Eokumente und EinstellungenDennisEigene DateienDatabaseoexport
    it is running from
    Eokumente und EinstellungenDennisEigene Dateien (german My Documents)

    and of course I get the error message, that ncftp is not found in that directory - very odd behaviour I can't explain

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Output report to HTML and run batch file? (200

    I'm afraid I don't know much about batch files, so I have no further ideas. If nobody else replies, we can call in the help of John 'Batman' Gray.

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Output report to HTML and run batch file? (200

    Can you just change the batch file so that itincludes the full path to ncftp in its command line?

    @echo off
    cathncftp -u myuser -p mypass -v 62.116.49.180 /test files/*
    Regards
    John



  10. #10
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output report... UPDATE!

    Ok!

    I tried using ShellExecuteAPI, but I don't know how to run that EXE since it's not opening. TXT files and CMD do work....

    Anyhow I'm getting closer to solve the problem:

    When running the command
    Shell CurrentProject.Path & "oexportncftp.exe" & " -u myuser -p mypassword 62.116.49.180 /test files/*", vbNormalFocus

    The only problem which exists is: Access sets the WORKING DIR to My Documents!!!! (Just like I could set the working dir when creating a Shortcut to an EXE file)

    I tried now to do this:
    Shell CurrentProject.Path & "oexportncftp.exe" & " -u myuser -p mypassword 62.116.49.180 /test " & CurrentProject.Path & "/oexport/files/*", vbNormalFocus

    And this COULD work, if Access WOULD return the CurrentProject.Path in 8:3 DOS naming standards, because the program doesn't understand long pathnames (COCUME~... instead of Cocuments and Settings...)

    Now either you tell me how to set another working dir or how to tell Access to use other Naming standards.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Output report... UPDATE!

    Copy the following code (you can see where I got it from - useful site) into a standard module:

    <img src=/w3timages/blueline.gif width=33% height=2>

    Private Declare Function GetShortPathName _
    Lib "kernel32" Alias "GetShortPathNameA" _
    (ByVal lpszLongPath As String, _
    ByVal lpszShortPath As String, _
    ByVal lBuffer As Long) As Long

    Public Function GetShortPath(strFileName As String) As String
    'KPD-Team 1999
    'URL: http://www.allapi.net/
    'E-Mail: KPDTeam@Allapi.net
    Dim lngRes As Long, strPath As String
    'Create a buffer
    strPath = String$(165, 0)
    'retrieve the short pathname
    lngRes = GetShortPathName(strFileName, strPath, 164)
    'remove all unnecessary chr$(0)'s
    GetShortPath = Left$(strPath, lngRes)
    End Function

    <img src=/w3timages/blueline.gif width=33% height=2>

    You can use it as follows:

    Dim strShortPath As String
    strShortPath = GetShortPath(CurrentProject.Path)
    Shell strShortPath & ...

  12. #12
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output report... UPDATE!

    wohooooow!

    Took a long time, but finally... [img]/forums/images/smilies/smile.gif[/img] Thank you very much Hans!

Posting Permissions

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