Complicated Data Import (1 Viewer)

[The_Doc_Man]

To move files, you can use the FileSystemObject method called .MoveFile...

MoveFile method (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

 

[theDBguy]

How would I move a file from one drive to another.

One other way is to use the Name As statement. For example,

Name OldFileName As NewFileName

 

[HotelierDream]

One way is to use the Name As statement. For example,

Name OldFileName As NewFileName

I would do this but how would I lookup the name of the file in the directory? It is not the same

 

[The_Doc_Man]

Note that NameAs doesn't have to ability to move a file to a different volume, whereas FSO's .MoveFile does (if the O/S and security settings would allow it.)

You can use the FileSystemObject to examine a directory and get a list (technically, a collection) of files in that directory. If you can recognize the name, FSO can help you there.

 

[theDBguy]

Note that NameAs doesn't have to ability to move a file to a different volume, whereas FSO's .MoveFile does (if the O/S and security settings would allow it.)

Thanks! For some reason, I don't see that tidbit mentioned here. It did say you can move a file to a different drive though.

Name statement (VBA)

Office VBA reference topic

docs.microsoft.com

 

[HotelierDream]

So I figured out a few things today, 1 how BAT files work, 2 how to use VBA to write bat files and 3, how to get really good at nesting. That all being said I was able to get the job done with 2 command buttons, a text box and a bit of coding. I am posting the latest version of code if anyone ever needs it. I am actually kind of proud of myself for figuring it out.

Anyway, now I need to figure out how to make sure I dont duplicate information. My text9 textbox is assigned to a separate table named LOG that logs the date the data was imported for and the date the action was completed. I need a way to stop my code from going through if text9 = any value in the field audit date in the table log. I also need to program a msg to show when it is a duplicate date so that the user knows.

Any ideas?

_____see below for code_____

Option Compare Database

Private Sub Command0_Click()
CurrentDb.Execute "del1"
CurrentDb.Execute "del2"
CurrentDb.Execute "del3"
CurrentDb.Execute "Ckbaldel"
CurrentDb.Execute "Trialdel"
CurrentDb.Execute "TrxCodeDel"
CurrentDb.Execute "TrxTrypeDel"
DoCmd.RunSavedImportExport ("market")
DoCmd.RunSavedImportExport ("finrpt")
CurrentDb.Execute "mktquery"
CurrentDb.Execute "TransQ"
CurrentDb.Execute "LedgersQ"
End Sub

Private Sub Command11_Click()
'PURPOSE: Create A New Text File
'SOURCE: www.TheSpreadsheetGuru.com
'Finrep-trialbal
'mktseg-market stats

Dim TextFile As Integer
Dim FilePath As String
Dim ABC As String

    ABC = Me.Text9
    ABC = Format(Me.Text9, "mmddyy")
  FilePath = "C:\Users\Public\ReportUpload.bat"
  TextFile = FreeFile
  Open FilePath For Output As TextFile
Print #TextFile, "@echo off"
Print #TextFile, "del" & " " & """C:\XML\mktseg\*.xml"""
Print #TextFile, "del" & " " & """C:\XML\FinRpt\*.xml"""
Print #TextFile, "Copy" & " " & "X:\MICROS\OPERA\export\OPERA\rkpcs\audit\" & ABC & "\" & "023*.xml" & " " & "C:\XML\mktseg"
Print #TextFile, "Copy" & " " & "X:\MICROS\OPERA\export\OPERA\rkpcs\audit\" & ABC & "\" & "159*.xml" & " " & "C:\XML\finrpt"
Print #TextFile, "ren C:\xml\mktseg\*.xml mrksegup.xml"
Print #TextFile, "ren C:\xml\finrpt\*.xml finrepup.xml"
  Close TextFile
 
  Shell ("C:\Users\Public\ReportUpload.bat")
 
End Sub

 

[CJ_London]

use a dcount function

if dcount("*","myTable","filedateformatted='" & ABC & "'")=0 then
  'ok to to import
else
  'message
end if