Page 1 of 6 123 ... LastLast
Results 1 to 15 of 77
  1. #1
    Join Date
    Feb 2006
    Posts
    213

    Unanswered: Export a query and ask for a file name

    Ive compiled some code to import a file, run a query then export it to a file that the user chooses a name for. The problem is.. it doesnt work. The file imports, the queries are run, but once the user is prompted for a name something goes wrong and the wrong name is assigned to the file. Also the queries fail to be saved.

    I'll attach the database and some test data to this post.
    Attached Files Attached Files
    Last edited by marleyuk; 02-27-06 at 05:05.

  2. #2
    Join Date
    Feb 2006
    Posts
    213
    Ok.. im trying to get the statement in this sub:

    Code:
    Private Sub Command3_Click()
    
     Dim strFilter As String
     Dim lngFlags As Long
     Dim strInputFilename As String
     Dim ReturnResult As String
     Dim strMsg As String
     
     
     strFilter = ahtAddFilterItem(strFilter, "Infoworks Files (*.csv)", "*.CSV")
     strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
     
     ReturnResult = ahtCommonFileOpenSave(InitialDir:="C:\", _
     Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
     DialogTitle:="Please select your Infoworks file")
    
     DoCmd.TransferText acImportDelim, "", "tblStagingTable", ReturnResult, True, ""
     
     Call InsertData("tblStagingTable", "tblImportTableTest")
    
     DoCmd.OpenQuery "TotalNumberOfTimesteps"
     
     strMsg = "Please enter a name for the file to be saved as "
     pFilename = InputBox(Prompt:=strMsg, _
     Title:="File namer", XPos:=2000, YPos:=2000)
     
     ExportDelimitedText "TotalNumberOfTimesteps", pFilename
      
    End Sub
    To run the process in this sub:

    Code:
    Sub ExportDelimitedText( _
       pRecordsetName As String, _
       pFilename As String, _
       Optional pBooIncludeFieldnames As Boolean, _
       Optional pBooDelimitFields As Boolean, _
       Optional pFieldDeli As String)
    
          'BASIC USEAGE
            '  ExportDelimitedText "QueryName", "c:\path\filename.csv"
      
       'set up error handler
       On Error GoTo ExportDelimitedText_error
       
       Dim mPathAndFile As String, mFileNumber As Integer
       Dim r As Recordset, mFieldNum As Integer
       Dim mOutputString As String
       Dim booDelimitFields As Boolean
       Dim booIncludeFieldnames As Boolean
       Dim mFieldDeli As String
    
       booDelimitFields = Nz(pBooDelimitFields, False)
       booIncludeFieldnames = Nz(pBooIncludeFieldnames, False)
       
       'make the delimiter a TAB character unless specified
       If Nz(pFieldDeli, "") = "" Then
          mFieldDeli = Chr(9)
       Else
          mFieldDeli = pFieldDeli
       End If
       
       'if there is no path specfied, put file in current directory
       If InStr(pFilename, "\") = 0 Then
          mPathAndFile = CurrentProject.Path
       Else
          mPathAndFile = ""
       End If
       
       mPathAndFile = mPathAndFile & "\" & pFilename
    
       'if there is no extension specified, add TXT
       If InStr(pFilename, ".") = 0 Then
          mPathAndFile = mPathAndFile & ".txt"
       End If
       
       'get a handle
       mFileNumber = FreeFile
       
       'close file handle if it is open
       'ignore any error from trying to close it if it is not
       On Error Resume Next
       Close #mFileNumber
       On Error GoTo ExportDelimitedText_error
       
       'delete the output file if already exists
       If Dir(mPathAndFile) <> "" Then
          Kill mPathAndFile
          DoEvents
       End If
       
       'open file for output
       Open mPathAndFile For Output As #mFileNumber
       
       'open the recordset
       Set r = CurrentDb.OpenRecordset(pRecordsetName)
       
       'write fieldnames if specified
       If booIncludeFieldnames Then
          mOutputString = ""
          For mFieldNum = 0 To r.Fields.Count - 1
             If booDelimitFields Then
                 mOutputString = mOutputString & """" _
                   & r.Fields(mFieldNum) & """" & mFieldDeli
                Else
                   mOutputString = mOutputString _
                      & r.Fields(mFieldNum).Name & mFieldDeli
                End If
          Next mFieldNum
          
          'remove last delimiter
          mOutputString = Left(mOutputString, Len(mOutputString) - Len(mFieldDeli))
          
          'write a line to the file
          Print #mFileNumber, mOutputString
       End If
       
       'loop through all records
       Do While Not r.EOF()
       
          'tell OS (Operating System) to pay attention to things
          DoEvents
          mOutputString = ""
          For mFieldNum = 0 To r.Fields.Count - 1
             If booDelimitFields Then
                Select Case r.Fields(mFieldNum).Type
                   'string
                   Case 10, 12
                      mOutputString = mOutputString & """" _
                         & r.Fields(mFieldNum) & """" & mFieldDeli
                   'date
                   Case 8
                      mOutputString = mOutputString & "#" _
                         & r.Fields(mFieldNum) & "#" & mFieldDeli
                   'number
                   Case Else
                      mOutputString = mOutputString _
                         & r.Fields(mFieldNum) & mFieldDeli
                End Select
             Else
                mOutputString = mOutputString & r.Fields(mFieldNum) & mFieldDeli
             End If
             
          Next mFieldNum
          
          'remove last TAB
          mOutputString = Left(mOutputString, Len(mOutputString) - Len(mFieldDeli))
          
          'write a line to the file
          Print #mFileNumber, mOutputString
             
          'move to next record
          r.MoveNext
       Loop
    
       'close the file
       Close #mFileNumber
       
       'close the recordset
       r.Close
       
       'release object variables
       Set r = Nothing
       
       MsgBox "Done Creating " & mPathAndFile, , "Done"
       
       
       Exit Sub
       
    'ERROR HANDLER
    ExportDelimitedText_error:
       MsgBox Err.Description, , "ERROR " & Err.Number & "   ExportDelimitedText"
       'press F8 to step through code and correct problem
       Stop
       Resume
    End Sub
    This should be calling the variable 'pFilename' from the 2nd sub and running that process but im just getting errors like Byref arguement type mismatch. Anyone spot why?

    Thanks,
    Marley

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You need to dimension pFilename as a string. Otherwise it is being created as a variant and you are passing it to a string. Either Dim it as string, or convert it to a string with CStr().

    I would also check to make sure the user entered something in the pFilename. If the user presses enter at the InputBox without entering anything your code will not work. Hopefully I am telling you something you already know.

  4. #4
    Join Date
    Feb 2006
    Posts
    213

    update

    Right.. ive restructured the code as follows
    Code:
    Function createtext()
    Dim strFileName As String
    Dim FileToCreate As String
    Dim NewfileName As String
    Dim NewFileType As String
    
    strFileName = InputBox("Enter the name and the path you want for the file." & Chr(13) & "Example: C:\FileName")
    FileToCreate = strFileName & ".txt"
    
    DoCmd.TransferText acExportDelim, , "TableOrQueryName", FileToCreate, 1
    NewFileType = strFileName & ".txt"
    
    End Function
    But im still getting problems. The first is runtime error 2135 - this property is read only and cant be set. This error occurs during the export process, the file is exported but this error needs to do. The second problem is formatting, i want to add multiple queries to the one file in the one process. Also i want the text file to have titles for the imported data so it doesnt just appear as random numbers and the user can identify what it is they are seeing.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Eeek - (re the PM) against my better judgement I'll help - but let's try not to create the second longest thread in this forums history this time Not much scares me on this forum but..

    Ok - let's start at the end -
    Quote Originally Posted by marleyuk
    i want to add multiple queries to the one file in the one process
    Have a think about what your code does. It exports the data in a csv file. Are you sure you want to put all your queries into this one file? Try manually pasting the results of your queries into the file as you would like to appear and then try to make whatever use you intend to make of them (e.g. try opening in excel perhaps). Are you still sure you want to do this?
    Last edited by pootle flump; 02-28-06 at 08:12.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2006
    Posts
    213
    i promise we'll keep this short and sweet

    Well i need a summary of querys in one final, this is definate. The smaller the file size for this the better.

    The reason for the exports is so they can be saved and printed at a later date.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    DELETED - Dup Post.

    Lol - I'm not helping keep the post count down eh?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by marleyuk
    Well i need a summary of querys in one final, this is definate. The smaller the file size for this the better.

    The reason for the exports is so they can be saved and printed at a later date.
    How come in one file? And why csv of all formats?

    And why do you feel you have to export the data in order to archive and print it out at a later date?

    I think the ideal would be to give me a process flow of what you will actually do with the file.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2006
    Posts
    213
    All in one file because its a set of queries from one file..

    I want small backup copies that can be easily moved and printed hense the .txt


    Process flow =

    .csv file imported --> Queries ran on table --> Results exported to file as small clear, formatted summary of results.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    ok - why not keep the data in the database (archive it)? Why export it at all?

    For process flow I was thinking more after the file has been exported.

    The reasonI am asking all this is that I am trying to establish why you want to do this. Putting n set of results into one CSV file doesn't make much sense to me especially as you want a formatted report - is it simply because that is all I gave you last time? Would excel be adequate to store the results?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2006
    Posts
    213
    the database maybe used on a couple of different computers (not networked) so i feel this is the best route. Well i support excel would be ok, is it easier to format, do file sizes get very big??

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by marleyuk
    the database maybe used on a couple of different computers (not networked) so i feel this is the best route.
    Ah - so they are stand alone and not connected to the network?

    Quote Originally Posted by marleyuk
    Well i support excel would be ok, is it easier to format, do file sizes get very big??
    Easier to format than a csv? Yes
    They do if you cram lots of data into them otherwise no

    As I recall your queries return a small sub-set of your original data so they will be significantly smaller than that file.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Feb 2006
    Posts
    213
    Ah great. Any chance you giving me a hand with the formatting of a file then? I want to add 3 queries to the spreadsheet and make it look as well presented as i can.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    As it happens formatting of Excel via automation is something I consider a bit of a specialty of mine However, some steep learning curves ahead... Let me dig up some old posts.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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