Tech Support Guy banner
Status
Not open for further replies.

Editing downloaded data

Solved 
6K views 209 replies 5 participants last post by  OBP 
#1 ·
Tech Support Guy System Info Utility version 1.0.0.4
OS Version: Microsoft Windows 10 Home, 64 bit
Processor: Intel(R) Core(TM) i3-3227U CPU @ 1.90GHz, Intel64 Family 6 Model 58 Stepping 9
Processor Count: 4
RAM: 3977 Mb
Graphics Card: Intel(R) HD Graphics 4000, -2043 Mb
Hard Drives: C: 454 GB (402 GB Free);
Motherboard: Dell Inc., 033MX4
Antivirus: Windows Defender, Enabled and Updated
> I added this: I am using Windows Office Pro 2016
Am I going to have to do this each time I post?

I download data from Brisnet daily which I have to edit in order to use it. Since I started this, I have been doing it manually which is too time consuming. I am here to see if it can be automated and,if so, how?

Here are 2 files on Notepad. "Unedited" is as it downloads and "HOU" is after editing. I do this to put it into ACCESS and query for results. Now, if someone shows me how I can download the data directly into ACCESS as a usable table, I will name my next child for him/her! Automating this process will save plenty of time, so let us not explore that right now.

If you need additional info, ask.
 

Attachments

See less See more
#102 ·
OK, I did not look at the images that you posted closely enough.
You have placed the code in the Form's "On Current" Event procedure instead of the Button's On Click Event procedure.
So now you know that you can do things when the form is opened using VBA.
You need to go back in to form design mode and click on the Button and the "On click" event and then cut & paste the code from the Form's On Current event in to the Button's on click event.
They should both be listed.
 
#110 ·
Yes!.
One last thing before I have to go, it is 10:15pm here.
Close the message, go into the Button code and replace
msgbox "hello"
with
msgbox me.Field1
(I think your table's field name defaulted to Field1 when you created it)
When you click the button it should now show you the Path in your Table and form.
 
#113 ·
Hello.
I won't be able to spend so much time on this today, Sunday & Tuesday Nights are my Texas Holdem Poker Nights at my local Pub.
Assuming the code worked to display the Path to your file in the Message box we know that your Access 64bit version works with some VBA code, so let's see if it can do some of the critical stuff to automate you Import either on import or after import.
First of all let's see if your version can set some text to the File Path and then use that to open and close your text file.
So open the Form, press Alt + F11 to open the VBA Editor and replace the current msgbox me.Field1
with the following code

Dim strFile As String
strFile = Me.Field1
Open strFile For Input As #1
Close #1

MsgBox "opened file OK"

The Form has to be open in Form mode for the code to read the contents of Field1.
The first line Dim strFile As String tells Access that we want to establish some memory to a Variable called strFile in a String (Text) format.
The second line then sets that Text Variable to hold your File Path in Field1
The third line tries to open the the file in that path for reading in the data.
The fourth line just closes it again without bothering actually reading any data.
We could skip the strField path and just use the Me.Field1 directly in line 3 but it is useful to know whether or not we can assign the path to a text variable for later.
After changing the code you can press Alt F11 to go back to the form and either then save it or you can click the Button to see what happens (fingers crossed it will work).
Let me know how it goes, preferably with another screenshot as they are very useful, if I look at them carefully enough that is.
 
#121 ·
He and anyone else reading the thread can learn how Access & Access VBA works.
It is my time that is being used and I am not being paid for it, so I can spend as long as I like doing so.
If you don't like it please stop reading it.
 
#122 ·
Task for today, to import data from the file in using Line Input which brings in one record at a time.
If that works Ok we will try and parse it using a special Variable Array which splits the Record in to it's Fields.

So we add a line of code between opening & closing the text file like this

Open strFile For Input As #1
Line Input #1, strInput
Close #1

To test it we change the Message to
MsgBox "opened file OK and imported - " & strInput

To be technically correct we should also tell Access what strInput is used for so we add it to the Dim statement

Dim strFile As String, strInput as String
 
#123 ·
You are speaking a foreign language to me; I need it put much more simply. Remember I have no background nor knowledge of this other than the simple tasks that I have been perfoming.
Which of these do I use and where do I go to get what you want?

As I have said before, you are talking high school stuff to a 1st grader.
 

Attachments

Status
Not open for further replies.
You have insufficient privileges to reply here.
Top