Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Module help (Access2k)

    Hi Princess,

    What is the question ?
    DoCmd.TransferSpreadsheet save a file on your HD whith the data comming from your table.
    Exporting whith a macro or with code make no difference.
    Francois

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

    Re: Module help (Access2k)

    The current code transfers spreadsheet and saves it as whatever

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

    Re: Module help (Access2k)

    If thats not possible, Is it possible to rename a excel file from access using similar code???

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Module help (Access2k)

    <hr>This is because the program I am running this code from does not like me transferring data using code <hr>
    What do you mean by 'not like'. Do you get an error? Witch one?

    If you export by macro, the filename has to be recorded in the macro and you can't change it at runtime.
    If you have to use a different filename at each run, you have to use code to generate the filename and export the data.
    Francois

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Module help (Access2k)

    If you want to rename a file you can use the command Name.

    Name "c:MydirOldfile" as "c:MydirNewFile"
    Francois

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

    Module help (Access2k)

    Hi guys,
    As you can see in these piece of code, I am saving a file and transferring the data into that file right??
    Is there any way I could transfer the data and then save it after transferring the data??
    This is because the program I am running this code from does not like me transferring data using code so I have to use a macro to transfer the data instead.
    I am no good at VB coding so please please please <img src=/S/help.gif border=0 alt=help width=23 height=15> me..
    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

    <pre>
    'Set value to TDate
    Set rstCitco = db.OpenRecordset("tblSFMReportSource", dbOpenDynaset)
    With rstSFM
    TDate = rst![TradeDate]
    End With
    'Export records to spreadsheet and open it
    strFilename = CSTR_SAVEPATH & "BCP" & Format(TDate, "DDMMYY") & ".xls"
    vResult = Dir(strFilename)
    If vResult <> "" Then
    vResult = MsgBox("File " & strFilename & _
    " already exists, Would you like to overwrite that file?", vbYesNo + vbMsgBoxSetForeground)
    If vResult = vbYes Then

    'DoCmd.RunMacro "mcrTransferSFMTradeReport", , ""
    DoCmd.TransferSpreadsheet acExport, 5, "SFMTradeReport", strFilename, False, ""

    MsgBox "Data has been exported successfully.", vbInformation + vbMsgBoxSetForeground, _
    "Export Confirmation"
    Else
    strFilenamePart = InputBox("File " & strFilename & " already exists," _
    & Chr(10) & "Please enter another filename not including " _
    & Chr(34) & ".xls" & Chr(34) & ": ")
    If strFilenamePart = "" Then Exit Sub
    strFilename = CSTR_SAVEPATH & strFilenamePart & ".xls"
    DoCmd.TransferSpreadsheet acExport, 5, "SFMTradeReport", strFilename, False, ""
    MsgBox "Data has been exported successfully.", vbInformation + vbMsgBoxSetForeground, _
    "Export Confirmation"
    End If
    Else
    DoCmd.TransferSpreadsheet acExport, 5, "SFMTradeReport", strFilename, False, ""
    MsgBox "Data has been exported successfully.", vbInformation + vbMsgBoxSetForeground, _
    "Export Confirmation"
    </pre>


    Edited by Charlotte to correct horizontal scrolling

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

    Re: Module help (Access2k)

    This command doesn't work when this file exists and when I wanna rename it.
    Is there any commands for Renaming???
    Thanx Francois.
    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Module help (Access2k)

    This is the renaming command.
    If you issue the Name command just after the export or runmacro command, it is possible that Access try to rename the file while the export is not finished. Insert a DoEvents between the two lines.

    I still not understand why you use the run macro instead of docmd.transferspreadsheet ?
    Francois

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

    Re: Module help (Access2k)

    I was using the DoCmd.TranferSpreadsheet but when I link the program with the main program my company uses it gives me all these error messages.
    Thanx Francois.
    Im confused myself.
    Have to talk to those who designed the main program <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

Posting Permissions

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