I am working on an Excel VBA Application that Imports

[DwightCook]

Hello,
I am working on an Excel VBA Application that imports A CSV file every 2 minutes. Right before it imports the program clears sheet1 and then imports the Data. The CSV file is updated every 2 minutes as well. The file name changes daily, so here is my problem I want to be able to import automatically every day every two minutes. I have everything working except the automatic import with the auto name finding. The part of the program that I am stuck on is listed below. The way I need it to work is to import the "Availability" this has pulled in all the information from the file Path.
Any help you can give me would be appreciated
Thanks
Dwight

 
Dim myDir As String, fn As String, a(), n As Long, Availability As String
Dim myDate As Date, temp As Date
Private Sub Import()
 
 
myDir = "Q:\Manufacturing\Equipment\DispatchLogs\logs\7-DES\"
fn = Dir(myDir & "\*.csv")
 
 
Do While fn <> ""
    temp = CreateObject("Scripting.FileSystemObject").Getfile(myDir & "\" & fn).DateLastModified
    If myDate = 0 Then
        myDate = temp: Availability = myDir & "\" & fn
    Else
        If myDate < temp Then myDate = temp: Availability = myDir & "\" & fn
    End If
    fn = Dir
Loop
If Len(Availability) Then
      If vbYes = MsgBox("Opening Availability File  Name : " & Availability & vbLf & _
    "Last modified on : " & myDate, vbYesNo) Then
 
 
 
    End If
 
End Sub
 

[zmbd]

So what exactly is wrong with your code?

Posting code that appears to have had very little if any troubleshooting performed, and expecting someone to help doesn't usually result in much of an answer and may result in your thread being deleted.

Instead, please tell us what you were expecting to happen, what actually happened, for each error: the EXACT title, error number, and descriptions that occurred and at what line in your posted code the error occurred. These are the minimum requirements for posting a question of this nature.

You tell us nothing about how the file names are created.

You also tell us nothing about how the target directory is being used: are there other files being dumped into the folder, do you need to keep the orginal files, etc...?

for example, I have a LIMS database that goes out to our network, there is a single directory where pH-Meters and other instruements dump generic text files. Only the content of the files matter; thus, I pull the current file names into an array, parse the names from the array to the import function that opens and imports the file contents... because we have to keep the original data, the script then moves the old file out of the dump directory to an archive directory... does this every 2 minutes, or whenever it finishes parsing the current array - which ever is the latter of the two.

[DwightCook]

There are no errors, the program is incomplete.If you look on line 16 you will see (If myDate < temp Then myDate = temp: Availability = myDir & "\" & fn)This part of the program pulls all the information about the directory and the file name for the day and store the information in (Availability)
I dont know how to write the code that will Import (Availability)
Thanks
Dwight

[zmbd]

If you look on line 16

and you define

fn = Dir(myDir & "\*.csv")

earlier in the code.
What happens when there are two or more files in the directory, this code will most likely fail to function as expected.

Also

If myDate < temp Then myDate = temp: Availability = myDir & "\" & fn
 

although allowed would be better written:

   If myDate < temp Then 
      myDate = temp
      Availability = myDir & "\" & fn
   EndIf

Having two commands per line doesn't help the parser,
is harder to troubleshoot, easily missed by anyone that follows you to maintain the code, and overall detracts from readability of the code.

the program is incomplete

Very well aware of that... it didn't take me more than a quick glance to determine that your code wasn't quite right.
You also left out quite a bit of detail that I asked for in my last post, and even gave you an example to show the importance of having that information.

I dont know how to write the code that will Import (Availability)

Start with this link: DoCmd.TransferText Method (Access/Office 2010) - ignore the example about the word mail-merge at the bottom of this link... who knows why that is there as it has nothing to do with the method.

I have also used the basic IO methods to read the files in line by line and write directly to the open recordset: VBA Standard Text File I/O Statements

We can help you to correct errors, clear-up theory and application, and can/will even show you some of the more esoteric methods – some of these are readily available in our Insights Articles ( Microsoft Access / VBA Insights Sitemap ); however, we do ask that you write the code or design the project first. (^_^)

[DwightCook]

Thanks for your Help, I will look through these and reply with what I find.
Thanks
Dwight

[DwightCook]

Ok so here is what I have found so far:

Dim myDir As String, fn As String, a(), n As Long, Availability As String
Dim myDate As Date, temp As Date
Sub Import()
 
 
myDir = "Q:\Manufacturing\Equipment\DispatchLogs\logs\7-DES\"
fn = Dir(myDir & "\*.csv")
 
 
Do While fn <> ""
    temp = CreateObject("Scripting.FileSystemObject").Getfile(myDir & "\" & fn).DateLastModified
    If myDate = 0 Then
        myDate = temp: Availability = myDir & "\" & fn
    Else
        If myDate < temp Then 
        myDate = temp: 
        Availability = myDir & "\" & fn
    End If
    fn = Dir
Loop
If Len(Availability) Then
      If vbYes = MsgBox("Opening Availability File  Name : " & Availability & vbLf & _
    "Last modified on : " & myDate, vbYesNo) Then
 
   DoCmd.TransferText acImportDelim, "", "Sheet1", "Availability", True, ""
    End If
End If
 
End Sub
 

now I am getting a run-time error 2046
The Command or Action "TransferText" isn't available now.
I have tried several different way of writing this code with no luck.
The last statement should import a CSV file (Line 6) from the above directory using the Variable "Availability",
Well that is what I need it to do.
Thanks
Dwight

[zmbd]

Sorry, the docmd is an Access method not for Excel.

To make up for my mistake, try the following as a model,
Using the In Excel2010 you can make use of the QueryTable method which makes the importing of data slick. You'll need this as a reference: _QueryTable Properties (OFF2010)

I used a tab delimited csv text file with double quotes as the string demark.... the following should be fairly self explanatory.

Option Explicit
Sub ImportTextFileToWorksheet()
'
'
    Dim zstrFile As String
    '
    zstrFile = "TEXT;C:\Users\a109077\Documents\Working.csv"
    '
    With ActiveSheet.QueryTables _
        .Add _
        (Connection:=zstrFile, _
            Destination:=Range("$A$1"))
        .Name = "ztempImport"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 3, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    ActiveSheet.QueryTables.Item("ztempImport_1").Delete
End Sub
 

SO, what I did was open a connection to the file, as I would any other datasource, save the data to the worksheet setting things as I go...

NOTE: I BUILD THE CONNECTION STRING FIRST:
zstrFile = "TEXT;C:\Users\a109077\Documents\Working.csv"

This allows me to debug the string incase something gets mucked-up... and (IMHO) will be what you should do to build your files' path too,

Line #35: TextFileColumnDataTypes
I use the actual numeric here, easy enough to find, just <ctrl><g> and type ?xlSkipColumn which would yeld a 9, in the array I used, there is a "3" for "D/M/Y" date formated coulmn.
I highly advise you to use the type casting as I have in line35 to help ensure that your data is the proper type. If I could set the numeric type I would... and that "9" is very useful if you have a few columns of data you don't want to pull in!

For the very last line (line#39), I actually remove the connection to the outside file; thus, there should be no refresh errors or connection prompts etc...
NOTE: The name on line# 14.... note the item name in the last line of code... so, if you ran this code three times using "qryname" in line#14 you could have qryname_1, qryname_2, qryname_3, etc... so to remove them you have to either do a for/all/in-collection or append the correct count as I did to the name you give the querytable.

This is part of a code that I do use to handle some data imports... (that's where the array actually comes from in line 35 (^_^)) I've removed the error trapping and the other sensitive information; however, so long as you refere to a valid text file and change the array in 35 to match your file, this code will work.

Once again, sorry, I was on automatic for Access.