Append Information from a Website

[thechazm]

Yes it is possible but are you asking to return the
Filename: C:\Report_Data\Log Parser 8-10\MM9662\ENAProcessNotes.pnf

And extract out the MM9662 from that string?

The file itself with the data is easy enough to read but I need a little clarification on the MM9662 that you are refering to.

 

[LadyDi]

You are correct. What I really want to do, in addition to pulling in all the data, is extract the MM9662 out of the file name. If that is not possible, it can just return the full file name and I can extract the MM9662 through a query if needed.

 

[thechazm]

Ok if you send me a copy of your db layout I can write the code for you but I hope that you have been learning and how code can work and how to get to certain resources.

 

[LadyDi]

Thank you very much for your help. Yes, I have learned a ton about writing code. In addition to studying what you have posted, I purchased a text book (Visual Basic 2008 Comprehensive - from the Shelly Cashman series) that I have read through. Unfortunately though, I am stumped on the text file. I can find code for Visual Basic to open and read a text file, but that code doesn't work in VBA and I can't seem to find the alternative. I also have a limited time to work on this, my manager and my director want this done as soon as possible. This is one of those times, that they really wanted it done yesterday.

Here is the current structure of my database.

 

[thechazm]

Ok which table is this data going in?

 

[LadyDi]

The files would be going into the tblENASummary table. There is an auto number in that table and then the UNITID is the MM9662 that was discussed earlier. If that can't be pulled out, we could just put the file name in that column. Every other column matches the columns going across the text file.

 

[thechazm]

Ok here is the modified file.

I tested it against the example file that you sent and all works as far as I can tell.

Let me know if you have problems but I generated a function in the Excel Functions module called GetDatafromTextFile

Just call that function and it should take care of the rest.

 

[LadyDi]

That seems to work great. However, I have a couple questions. After the function runs, I get a "Type Mismatch" error. However, it has copied all the information, so I don't know why I am getting that message.

Also, I tried to add another If statement, so that it will only pull in the rows for a given date (i.e. only the records with a transaction date of 8/10/2012). I delcared a constant variable on my form, and then I tried to use that constant in this function. However, it isn't working. It is still copying all the data in the text file. Is there some kind of trick to get this to work with dates?

If CDate(xlsSheet.Cells(i, 2)) >= CDate(cstrDate) Then

I added the End If after the update section.

 

[thechazm]

A type mismatch error means that some of the data was left out. You really need to double check hard because there might be some abitrary data in the file.

The date function you are trying to do is not far off but you need to make sure your dates are in the same format for the comparision to work.

Example is if you want them both to be formated as a Short Date which strips out the time in the text file then do something like this.

If Format(Cdate(xlssheet.cells(i,2)), "Short Date") >= Format(CDate(cstrDate), "Short Date") then

That should get you what you want.

 

[LadyDi]

Okay, I got the date function to work like I need it to.

I also went through the data that was added with a fine tooth comb and compared it to the original text file. All of the data crossed correctly.

 

[thechazm]

Good to know and glad you got your logic working. I would keep an eye on the type mismatch and see if you can locate the line of code that is giving you that error but other than that congrats.

 

[LadyDi]

Thank you, I will keep an eye on this.

I have another question. The text files that I am uploading are all housed in separate files, so I can't select all the files at once. I am currently loading all the file names in a given folder and the files in any subfolders in a temporary table labeled Files2. I set up a query, titled "qryENAForLoop", to pull the text files only from that list. There is only one column in that query, labeled "ENAPath", and it includes the full file path (i.e. C:\Report_Data\Log Parser 8-10\MM9662\ENAProcessNotes.txt"). I would like to use that query and set up a loop to go through all of the files that need uploaded, instead of using the "BrowseFile" function. I thought I could figure this one out on my own, but again, it isn't working. I'm not sure if the loop needs to be in the GetDataFromTextFile function or if it needs to be on the form. I thought I could use a For Each Loop and set the query as an array variable. I can't seem to get the array set up to Access's satisfaction. I tried this DIM strFilePath () as String = "qryENAForLoop". That didn't work, so I tried DIM strFilePath () as String = [Queries]![qryENAForLoop]![ENAPath]. That didn't work either, and I don't know what else to try.

What do you suggest? Is this even possible to accomplish?

 

[thechazm]

Take a look at the function I revised in the example db I am posting.

If you use the function called ImportFilesfromQuery that opens a recordset with the information from the query that you specified. Then I changed the function GetDatafromTextFile to use the file name being supplied and run through each one.

You can load a query into an array but you have to setup the space in the array before you try to pass it data like I did with the Arr() array. So an example is I had to redim the Arr array before I tried to use it to store data by using this code.

ReDim Arr(1 To 100)

That sets up the Arr array to carry 100 entries in there from 1 to 100. Now this process can get even harder because when your working with queries or other datasets you may need to use 2-D array's which look something like this

ReDim Arr(1 To 100, 1 To 10)

Meaning now that array can hold 100 rows of data and 10 columns of data. Each field in a query or a table would be considered a column. The same goes for excel.

Hope this helps.

 

[LadyDi]

It doesn't look to me like you are treating the query like an array. You just created another function.

I ran the function you have for importing based on the query. It acts as though it is working, but when it is done nothing has been added to the table. I can't figure out why. Attached is the database, as it is now.

 

[thechazm]

You are correct the function was not putting it into an array I just opened it up in a recordset so I could scroll through it. I was not implying that my function had anything to do with an array so I apologies it sounded that way. I just wanted to give you an explanation on why your array was not working and give you some clue's.

Anyway I'll take a look at it and see whats up.

 

[thechazm]

I see what happend. You needed to replace your current GetDatafromTextFile function with the one I provided earlier. The one in the sample you just posted is the older function. I'll post the entire db with the changes back to save some problems.

 

[thechazm]

Here is the updated file. Sometimes with code you feel like doing :banghead: but hang in there. It's the simple things that can bite harder.

 

[LadyDi]

That works perfectly. Thank you so much for your assistance, patience, and understanding.

 

[thechazm]

No worries. Gotta love adventures

 

[LadyDi]

I have one more question for you. I am in the process of running these modules for over a thousand files, and keep getting an error message that my computer is out of memory. I looked at my task manager and even though Excel is not listed in the Applications tab, it is listed in the Processes tab. Is there a line we can add to the code that will end the Excel process? It seems as though it is opening each csv file, but is not ending any of them. Do you have any suggestions?