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
-
6 1825 zmbd 5,501
Expert Mod 4TB
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.
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 5,501
Expert Mod 4TB
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. (^_^)
Thanks for your Help, I will look through these and reply with what I find.
Thanks
Dwight
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 5,501
Expert Mod 4TB
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: ST |
last post by:
Hi, I'm having problems opening up excel thru my code. It will write and
saveas an excel file, but the application won't open on the user's computer,
excel seems to be hidden, because it shows up...
|
by: KC |
last post by:
Hi,
I have code similar to this..
Dim xlApp As Object
xlApp = CreateObject("Excel.Application", "\\MyServer")
The call is from a asp.net (Intranet) application. \\Myserver is a network...
|
by: ChrisBowringGG |
last post by:
When you use Application.Quit() on an Excel application,
there can still be an instance of Excel running,
as seen in Task Manager.
You can try following the advice on MSDN:
...
|
by: Fabrizio |
last post by:
Hi,
i'm tryng to build a ASP.net application that reads from an excel file .
I'm using the microsoft Excel 10.0 object library and office interop 10
library
Everything was ok untill 2 days ago...
|
by: Bernd Muent |
last post by:
Hi together,
I am using the following code in Visual Basic to open Word or Excel
applications:
Word:
Dim w As Word.Application
w = CType(CreateObject("Word.application"), Word.Application)...
|
by: Ultima |
last post by:
I'm trying to create an Excel file using a VB program. I have added the
reference to Excel Object Library yet when I try to compile it, I get
an error at the line exc_app =...
|
by: Doug Glancy |
last post by:
I got the following code from Francesco Balena's site, for disposing of Com
objects:
Sub SetNothing(Of T)(ByRef obj As T)
' Dispose of the object if possible
If obj IsNot Nothing AndAlso...
|
by: RohitGBhagwat |
last post by:
Hi, I am New To ASP.Net and I have created a small application in ASP.Net using C# which is a reporting Tool. In this Application I have to Edit an Excel File on a Weekly basis and for that i am...
|
by: prakashsakthivel |
last post by:
Hi Members,
One excel application (Excel 2003), I set range, formating it is working fine through macro..
After that as per client requirements, I changed Excel 2000 instead of Excel 2003....
|
by: Paul Morriss |
last post by:
I have an application that opens a Excel file into a form using AxWebBrowser.Navigate. This works fine unless there is another Excel file open before I run the application, in which both excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |