By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,617 Members | 1,172 Online
Bytes IT Community

Home Questions Articles Browse Topics Latest Top Members FAQ

home > topics > microsoft access / vba > questions > problem wiht text file export function
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,617 IT Pros & Developers. It's quick & easy.

Problem wiht text File Export Function

100+
P: 121
I have a somewhat simple question, but have been baffled by it for a while, and now I'm on a tight deadline - have to get it done within 24 hours.

I am trying to export a set of files to my hard drive to then later be FTPd to a server. the file format should be "TITLE.yyyymmdd" where yymmdd is the date code for when the files are created.

The files can't be .txt. But when I run the files as ".txt" the code runs just fine. when I change them to run to ".yyyymmdd" I get an error saying "Database or object is read only" and the debug points me to the line i have marked with a Bold Asterisk below.


Thanks for any help that can be shed


Here's the code I want to run, but can't:

Expand|Select|Wrap|Line Numbers
  1. Function Export_Ouput_Files()
  2. Dim NewFldr As String
  3. Dim DT_Fldr As String
  4. Dim DT_Code_ST As String
  5. Dim DT_Code As String
  6. Dim DT_YR As String
  7. Dim DT_TM As String
  8. Dim fs As Object
  9.  
  10. DT_Code_ST = Now()
  11. DT_YR = Format(DT_Code_ST, "yyyymmdd")
  12. DT_TM = Format(DT_Code_ST, "hhmmss")
  13.  
  14. DT_Code = DT_YR & "-" & DT_TM
  15. DT_Fldr = "C:\DLU\Outgoing\" & DT_YR & "\"
  16. NewFldr = DT_Fldr & DT_TM & "\"
  17.  
  18. Set fs = CreateObject("Scripting.FileSystemObject")
  19.  
  20.     If Not fs.FolderExists(DT_Fldr) Then
  21.         fs.CreateFolder DT_Fldr
  22.         MkDir NewFldr
  23.  
  24.     DoCmd.TransferText acExportDelim, "BP_ATNDE_FILE Export Specification", "BP_ATNDE_FILE", NewFldr & "BP-TEST." & DT_YR, False, ""
  25.     DoCmd.TransferText acExportDelim, "CD_DS_FILE Export Specification", "CD_DS_FILE", NewFldr & "CD_DS-TEST." & DT_YR, False, ""
  26.     DoCmd.TransferText acExportDelim, "CUST_INPT_Vality Export Specification", "CUST_INPT_Vality", NewFldr & "CUST-TEST." & DT_YR, False, ""
  27.     DoCmd.TransferText acExportDelim, "EVNT_FILE Export Specification", "EVNT_FILE", NewFldr & "EVNT-TEST." & DT_YR, False, ""
  28.     DoCmd.TransferText acExportDelim, "EXP_FILE Export Specification", "EXP_FILE", NewFldr & "EXP-TEST." & DT_YR, False, ""
  29.     DoCmd.TransferText acExportDelim, "INSN_ORGZN_Vality Export Specification", "INSN_ORGZN_Vality", NewFldr & "INST-TEST." & DT_YR, False, ""
  30.     DoCmd.TransferText acExportDelim, "PRD_FILE Export Specification", "PRD_FILE", NewFldr & "PRD-TEST." & DT_YR, False, ""
  31.  
  32.       Beep
  33.       MsgBox "All Files have Been Exported to: " & NewFolder, vbInformation, "Export Complete"
  34.  
  35.  
  36.     Else
  37.  
  38.         MkDir NewFldr
  39.  
  40.   *  DoCmd.TransferText acExportDelim, "BP_ATNDE_FILE Export Specification", "BP_ATNDE_FILE", NewFldr & "BP-TEST." & DT_YR, False, ""
  41.     DoCmd.TransferText acExportDelim, "CD_DS_FILE Export Specification", "CD_DS_FILE", NewFldr & "CD_DS-TEST." & DT_YR, False, ""
  42.     DoCmd.TransferText acExportDelim, "CUST_INPT_Vality Export Specification", "CUST_INPT_Vality", NewFldr & "CUST-TEST." & DT_YR, False, ""
  43.     DoCmd.TransferText acExportDelim, "EVNT_FILE Export Specification", "EVNT_FILE", NewFldr & "EVNT-TEST." & DT_YR, False, ""
  44.     DoCmd.TransferText acExportDelim, "EXP_FILE Export Specification", "EXP_FILE", NewFldr & "EXP-TEST." & DT_YR, False, ""
  45.     DoCmd.TransferText acExportDelim, "INSN_ORGZN_Vality Export Specification", "INSN_ORGZN_Vality", NewFldr & "INST-TEST." & DT_YR, False, ""
  46.     DoCmd.TransferText acExportDelim, "PRD_FILE Export Specification", "PRD_FILE", NewFldr & "PRD-TEST." & DT_YR, False, ""
  47.  
  48.       Beep
  49.       MsgBox "All Files have Been Exported to: " & NewFldr, vbInformation, "Export Complete"
  50.  
  51.     End If
  52.  
  53. End Function
  54.  
Apr 11 '07 #1
Share this Question
Share on Google+
5 Replies
pks00
Expert 100+
P: 280
Why dont u then just export it as a .txt file (TransferText accepts certain suffixes only)
then after export, rename it

eg

FileCopy "oldfile","newfile"
Kill "oldfile"
Apr 11 '07 #2
ADezii
Expert 5K+
P: 8,668
I have a somewhat simple question, but have been baffled by it for a while, and now I'm on a tight deadline - have to get it done within 24 hours.

I am trying to export a set of files to my hard drive to then later be FTPd to a server. the file format should be "TITLE.yyyymmdd" where yymmdd is the date code for when the files are created.

The files can't be .txt. But when I run the files as ".txt" the code runs just fine. when I change them to run to ".yyyymmdd" I get an error saying "Database or object is read only" and the debug points me to the line i have marked with a Bold Asterisk below.


Thanks for any help that can be shed


Here's the code I want to run, but can't:

Expand|Select|Wrap|Line Numbers
  1. Function Export_Ouput_Files()
  2. Dim NewFldr As String
  3. Dim DT_Fldr As String
  4. Dim DT_Code_ST As String
  5. Dim DT_Code As String
  6. Dim DT_YR As String
  7. Dim DT_TM As String
  8. Dim fs As Object
  9.  
  10. DT_Code_ST = Now()
  11. DT_YR = Format(DT_Code_ST, "yyyymmdd")
  12. DT_TM = Format(DT_Code_ST, "hhmmss")
  13.  
  14. DT_Code = DT_YR & "-" & DT_TM
  15. DT_Fldr = "C:\DLU\Outgoing\" & DT_YR & "\"
  16. NewFldr = DT_Fldr & DT_TM & "\"
  17.  
  18. Set fs = CreateObject("Scripting.FileSystemObject")
  19.  
  20.     If Not fs.FolderExists(DT_Fldr) Then
  21.         fs.CreateFolder DT_Fldr
  22.         MkDir NewFldr
  23.  
  24.     DoCmd.TransferText acExportDelim, "BP_ATNDE_FILE Export Specification", "BP_ATNDE_FILE", NewFldr & "BP-TEST." & DT_YR, False, ""
  25.     DoCmd.TransferText acExportDelim, "CD_DS_FILE Export Specification", "CD_DS_FILE", NewFldr & "CD_DS-TEST." & DT_YR, False, ""
  26.     DoCmd.TransferText acExportDelim, "CUST_INPT_Vality Export Specification", "CUST_INPT_Vality", NewFldr & "CUST-TEST." & DT_YR, False, ""
  27.     DoCmd.TransferText acExportDelim, "EVNT_FILE Export Specification", "EVNT_FILE", NewFldr & "EVNT-TEST." & DT_YR, False, ""
  28.     DoCmd.TransferText acExportDelim, "EXP_FILE Export Specification", "EXP_FILE", NewFldr & "EXP-TEST." & DT_YR, False, ""
  29.     DoCmd.TransferText acExportDelim, "INSN_ORGZN_Vality Export Specification", "INSN_ORGZN_Vality", NewFldr & "INST-TEST." & DT_YR, False, ""
  30.     DoCmd.TransferText acExportDelim, "PRD_FILE Export Specification", "PRD_FILE", NewFldr & "PRD-TEST." & DT_YR, False, ""
  31.  
  32.       Beep
  33.       MsgBox "All Files have Been Exported to: " & NewFolder, vbInformation, "Export Complete"
  34.  
  35.  
  36.     Else
  37.  
  38.         MkDir NewFldr
  39.  
  40.   *  DoCmd.TransferText acExportDelim, "BP_ATNDE_FILE Export Specification", "BP_ATNDE_FILE", NewFldr & "BP-TEST." & DT_YR, False, ""
  41.     DoCmd.TransferText acExportDelim, "CD_DS_FILE Export Specification", "CD_DS_FILE", NewFldr & "CD_DS-TEST." & DT_YR, False, ""
  42.     DoCmd.TransferText acExportDelim, "CUST_INPT_Vality Export Specification", "CUST_INPT_Vality", NewFldr & "CUST-TEST." & DT_YR, False, ""
  43.     DoCmd.TransferText acExportDelim, "EVNT_FILE Export Specification", "EVNT_FILE", NewFldr & "EVNT-TEST." & DT_YR, False, ""
  44.     DoCmd.TransferText acExportDelim, "EXP_FILE Export Specification", "EXP_FILE", NewFldr & "EXP-TEST." & DT_YR, False, ""
  45.     DoCmd.TransferText acExportDelim, "INSN_ORGZN_Vality Export Specification", "INSN_ORGZN_Vality", NewFldr & "INST-TEST." & DT_YR, False, ""
  46.     DoCmd.TransferText acExportDelim, "PRD_FILE Export Specification", "PRD_FILE", NewFldr & "PRD-TEST." & DT_YR, False, ""
  47.  
  48.       Beep
  49.       MsgBox "All Files have Been Exported to: " & NewFldr, vbInformation, "Export Complete"
  50.  
  51.     End If
  52.  
  53. End Function
  54.  
The problem is that 'yyyymmdd' is not a recognizable File Extension to be utilized with the TransferText() Method. Implement 'yyyymmdd' within the File's Base Name and use the .txt Extension.
Apr 11 '07 #3
pks00
Expert 100+
P: 280
eg

Expand|Select|Wrap|Line Numbers
  1. Function Export_Ouput_Files()
  2.  
  3.     Dim NewFldr As String
  4.     Dim DT_Fldr As String
  5.     Dim DT_Code_ST As String
  6.     Dim DT_Code As String
  7.     Dim DT_YR As String
  8.     Dim DT_TM As String
  9.     Dim fs As Object
  10.  
  11.     DT_Code_ST = Now()
  12.     DT_YR = Format(DT_Code_ST, "yyyymmdd")
  13.     DT_TM = Format(DT_Code_ST, "hhmmss")
  14.  
  15.     DT_Code = DT_YR & "-" & DT_TM
  16.     DT_Fldr = "C:\DLU\Outgoing\" & DT_YR & "\"
  17.     NewFldr = DT_Fldr & DT_TM & "\"
  18.  
  19.     Set fs = CreateObject("Scripting.FileSystemObject")
  20.  
  21.     If Not fs.FolderExists(DT_Fldr) Then
  22.         fs.CreateFolder DT_Fldr
  23.         MkDir NewFldr
  24.     End If
  25.  
  26.  
  27.     DoCmd.TransferText acExportDelim, "BP_ATNDE_FILE Export Specification", "BP_ATNDE_FILE", NewFldr & "BP-TEST.txt", False, ""
  28.     RenameSuffix NewFldr & "BP-TEST.txt", DT_YR
  29.  
  30.     DoCmd.TransferText acExportDelim, "CD_DS_FILE Export Specification", "CD_DS_FILE", NewFldr & "CD_DS-TEST.txt", False, ""
  31.     RenameSuffix NewFldr & "CD_DS-TEST.txt", DT_YR
  32.  
  33.     DoCmd.TransferText acExportDelim, "CUST_INPT_Vality Export Specification", "CUST_INPT_Vality", NewFldr & "CUST-TEST.txt", False, ""
  34.     RenameSuffix NewFldr & "CUST-TEST.txt", DT_YR
  35.  
  36.     DoCmd.TransferText acExportDelim, "EVNT_FILE Export Specification", "EVNT_FILE", NewFldr & "EVNT-TEST.txt", False, ""
  37.     RenameSuffix NewFldr & "EVNT-TEST.txt", DT_YR
  38.  
  39.     DoCmd.TransferText acExportDelim, "EXP_FILE Export Specification", "EXP_FILE", NewFldr & "EXP-TEST.txt", False, ""
  40.     RenameSuffix NewFldr & "EXP-TEST.txt", DT_YR
  41.  
  42.     DoCmd.TransferText acExportDelim, "INSN_ORGZN_Vality Export Specification", "INSN_ORGZN_Vality", NewFldr & "INST-TEST.txt", False, ""
  43.     RenameSuffix NewFldr & "INST-TEST.txt", DT_YR
  44.     INST -Test
  45.     DoCmd.TransferText acExportDelim, "PRD_FILE Export Specification", "PRD_FILE", NewFldr & "PRD-TEST.txt", False, ""
  46.     RenameSuffix NewFldr & "PRD-TEST.txt", DT_YR
  47.  
  48.     Beep
  49.     MsgBox "All Files have Been Exported to: " & NewFolder, vbInformation, "Export Complete"
  50.  
  51.  
  52. End Function
  53.  
  54. Public Sub RenameSuffix(ByVal sFile As String, ByVal sNewSuffix As String)
  55.  
  56.     Dim sNewFile As String
  57.  
  58.     sNewFile = Replace(sFile, ".txt", sNewSuffix)
  59.  
  60.     FileCopy sFile, sNewFile
  61.     Kill sFile
  62.  
  63. End Sub
  64.  
Apr 11 '07 #4
pks00
Expert 100+
P: 280
note rogue entry in between code I posted

this is just on its own, just remove it

INST -Test
Apr 11 '07 #5
100+
P: 121
Why dont u then just export it as a .txt file (TransferText accepts certain suffixes only)
then after export, rename it

eg

FileCopy "oldfile","newfile"
Kill "oldfile"

You know what - I feel like an idiot. I knew there would be a really easy solution. I just got so engrossed in trying to figure out why I was getting the error that I was ignoring other options. Oh well, that is why this forum is so great.


It works fine now - Thanks again!
Apr 11 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Question stats

  • viewed: 2863
  • replies: 5
  • date asked: Apr 11 '07
BYTES.COM © 2019 About Bytes | Advertise on Bytes | Privacy Policy | Manage Cookies
Sitemap | Microsoft Access / VBA Answers Sitemap | Microsoft Access / VBA Insights Sitemap

Follow us to get the Latest Bytes Updates