How to send csv file to the network drive automatically
automatically detect csv files is exist in the folder or not, if exist then
sent it to the mapped network drive and after sending successfully delete original csv file, and go for another csv file...........till all csv file sent success fully,
and every minute function will check csv file exist or not.
and i want all this process automatically
my approach is for just local drive
not for mapped network drive
Note : all csv file with different different name.
Dim txtdbpath As String 'this is your database file Dim txtdbpathcopy As String 'this is your copy path Dim intResponse, fs' the following double checks, Are you sure?' intResponse = MsgBox("Are you sure that you wish to make a copy of your database file now?", vbYesNo + vbDefaultButton1 + vbQuestion, "Crystal")' If (intResponse = 6) Then Set fs = CreateObject("Scripting.FileSystemObject")' txtdbpath = frmMain.cdgDialog.FileName' txtdbpath = "D:\Crystal\23456.csv" txtdbpath = App.Path & "\" & "23456.csv" txtdbpathcopy = "D:\" fs.CopyFile txtdbpath, txtdbpathcopy, True '''MsgBox "Backup Done Successfully", vbInformation, "Crystal"
The below code will get all the files in your folder
u can copy it to your map drive exactly same as local drive.
fs.deletefile will do the deletion for you.
Dim oFileSystem As New FileSystemObject Dim oFolder As Folder Dim oCurrentFile As File Dim oFileColl As Files Set oFolder = oFileSystem.GetFolder(“C:\Files”) Set oFileColl = oFolder.Files If oFileColl.Count > 0 Then With lstFiles For Each oCurrentFile In oFileColl .AddItem oCurrentFile.Name 'add item Next .ListIndex = 0 End With End If Set oFileSystem = Nothing Set oFolder = Nothing Set oFileColl = Nothing Set oCurrentFile = Nothing
oCurrentFile.Type will give you the filetype.. so just check it before accessing the file.
0
There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
If you want to run this as a job or scheduled task every minute, rather than having robocopy open all the time, then you can create a scheduled task to run ...
o.k. first of all do you want to do this in your access application?
or in vb/vbscript?
for access it's something like:
Dim txtdbpath As String 'this is your database file
Dim txtdbpathcopy As String 'this is your copy path
Dim intResponse, fs
Set fs = CreateObject("Scripting.FileSystemObject")
txtdbpath = CurrentProject.Path & "\" & "*.csv"
txtdbpathcopy = "D:\"
On Error Resume Next
fs.CopyFile txtdbpath, txtdbpathcopy, True
If Err = 0 Then
fs.DeleteFile txtdbpath
End If
Set fs = Nothing
following code is working fine from my vb6.0 application
Dim txtdbpath As String 'this is your database file Dim txtdbpathcopy As String 'this is your copy path Dim intResponse, fs' the following double checks, Are you sure?' intResponse = MsgBox("Are you sure that you wish to make a copy of your database file now?", vbYesNo + vbDefaultButton1 + vbQuestion, "Crystal")' If (intResponse = 6) Then Set fs = CreateObject("Scripting.FileSystemObject")' txtdbpath = frmMain.cdgDialog.FileName' txtdbpath = "D:\Crystal\23456.csv" txtdbpath = App.Path & "\" & "23456.csv" txtdbpathcopy = "D:\" fs.CopyFile txtdbpath, txtdbpathcopy, True '''MsgBox "Backup Done Successfully", vbInformation, "Crystal"
the vb6 code would be:
Dim txtdbpath As String 'this is your database file
Dim txtdbpathcopy As String 'this is your copy path
Dim intResponse, fs
Set fs = CreateObject("Scripting.FileSystemObject")
txtdbpath = App.Path & "\" & "*.csv"
txtdbpathcopy = "D:\"
On Error Resume Next ' dont stop on errors
fs.CopyFile txtdbpath, txtdbpathcopy, True 'copy all csv files
If Err = 0 Then ' only delete if copy was a success
fs.DeleteFile txtdbpath 'delete files
End If
Set fs = Nothing 'clean up filesystem object
When i am adding more fields
its split name like ( Tom Cruise ) its split into two field and
On insert command its giving me Run Time Error that Number of Query Values and Destination Fields aren't the same,
and highlighted on following line
con.Execute "insert into Bilty_Detail (BiltyNo,Mode,BDate,TruckNo,Code,Consignor,Consignee,From_City,To) values (" & valuelist & ")"
Problem is on following line
props = Split(line, " ") < ------- this line split name also and this is the problem thats why i am getting error
Const adOpenStatic = 3Const adLockOptimistic = 3Const ForReading = 1Const MDB_FILE = "D:\Crystal\Auto_Trans\Transport.mdb"Const TABLE_NAME = "Bilty_Detail"Const TABLE_COLUMNS = "BiltyNo,Mode,BDate,TruckNo,Code,Consignor,Consignee,From_City,To"''''Following fields need to add in TABLE_COLUMNS''',From_City,To,Article,Description,Weight,Rate,ToPay,Paid,Pymt_mode,CR,HC,AOC,BC,Others,Total,Pvt_Mark,Declaration_val,Delivery_at,Narration,GroupName,Service_Tex,User_Name,Chg_Weight,TotToPay,TotPaid,Art_Type,Rate_Type,CNorTinNo,CNeeTinNo,STaxPayBy,FOV,Door_Delv,Invo_No,Status_Rec,CnorPNo,CneePnoConst CSV_SEARCH_PATH = "D:\Crystal\Auto_Trans"Dim fso As New FileSystemObjectDim objConnection As New ADODB.ConnectionDim objRecordSet As New ADODB.RecordsetDim drive, objWMIService, path, line, objFile, oFile, i, props, colFiles, columnsSet objRecordSet = CreateObject("ADODB.Recordset")objRecordSet.Open "SELECT * FROM " & TABLE_NAME, _ con, adOpenStatic, adLockOptimisticSet objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")drive = Split(CSV_SEARCH_PATH, "\")(0)path = "\\" & Replace(Split(CSV_SEARCH_PATH, drive + "\")(1), "\", "\\") & "\\"Set colFiles = objWMIService.ExecQuery("Select * from CIM_DataFile where Drive='" + drive + "' and path = '" + path + "' and extension = 'csv'")For Each oFile In colFiles Set objFile = fso.OpenTextFile(oFile.Name, ForReading) Dim valuelist For Each line In Split(objFile.ReadAll, vbNewLine) objRecordSet.AddNew columns = Split(TABLE_COLUMNS, ",") '''''''''''''''''''''''''''''''''''''' props = Split(line, " ")For i = 0 To UBound(props)If Not IsNull(props(i)) And props(i) <> "" ThenIf valuelist = "" Thenvaluelist = valuelist & "'" & props(i) & "'"Elsevaluelist = valuelist & ",'" & props(i) & "'"End IfEnd IfNext iIf valuelist <> "" Then'objConnection.Execute "insert into Bilty_Detail (BiltyNo,Mode,BDate,from_City,To) values (" & valuelist & ")" con.Execute "insert into Bilty_Detail (BiltyNo,Mode,BDate,TruckNo,Code,Consignor,Consignee,From_City,To) values (" & valuelist & ")" '''',From_City,To,Article,Description,Weight,Rate,ToPay,Paid,Pymt_mode,CR,HC,AOC,BC,Others,Total,Pvt_Mark,Declaration_val,Delivery_at,Narration,GroupName,Service_Tex,User_Name,Chg_Weight,TotToPay,TotPaid,Art_Type,Rate_Type,CNorTinNo,CNeeTinNo,STaxPayBy,FOV,Door_Delv,Invo_No,Status_Rec,CnorPNo,CneePnoEnd If Next Next
If you simply want to move all CSV's to a different location -- reverting back to your original code, something like this doesn't work for you?
(Code below moves all CSV's to the Archive folder)
The only thing you'd need to do is set a task scheduler job to run the code every X minutes.
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.
One of a set of tools we're offering as a way to say thank you for being a part of the community.
u can copy it to your map drive exactly same as local drive.
fs.deletefile will do the deletion for you.
Open in new window