Readline data to be loaded from within VBA code?

[scriptscribe]

Hi fellas, I have an interesting quandry. Because of the security profiles of my company's network, I'm not able to access (upload/write to) any files via VBA. So the only way for me to write to our computer system via VBA is for me to cut and paste the entire text file in my code. I know...sucks for me.

My data looks like this:

NAME: SMITH MD,DAVID
NUMERIC CODE: 999
MNEMONIC: SMID
DIVISION: ANESTHESIOLOGY
BILLING AREA(S): ANESTHESIA SVCS
BILLING AREA(S): SUPPLY
LOCATION(S): INPATIENT
LOCATION(S): OUTPATIENT

Normally to access something like this from a network, I'd write code similar to this:

Sub ImportFile()
Dim fs As FileSystemObject
Dim f As TextStream

Set fs = CreateObject("Scripting.FileSystemObject")

strFileIn = "T:\strText.txt"

Set fin = fs.OpenTextFile(strFileIn)

Do While f.AtEndOfStream <> True

lin = f.ReadLine

If Trim(lin) Like "Billing Area:*" Then
    strLine1 = Trim(Right((lin), InStr(":", 1), 15))
    f.WriteLine strLine1
        If Trim(lin) Like "Name:*" Then
            strLine1 = Trim(Right((lin), InStr(":", 1), 25))
            f.WriteLine strLine1
        End If
End If

Loop

f.Close
Set f = Nothing
Set fs = Nothing
End Sub

So I'll just have about a hundred entries (at least) like the one above, that I'll need to loop through until I reach the next field that reads "Name:", which of course will signify the next doctor entry.

My question is, how do I identify the data (readline)when it's inside the VBA code, write the data after the ":" (not sure if I used Instr correctly) and loop through it?

I'm a bit new to VBA, so any help will be appreciated. I'm familiar with the basics, but this one has thrown me for a loop, as at previous companies I've always linked to spreadsheets or text files on the network drives.

Thanks,
Mike

 

[scriptscribe]

To be more specific, my data will be an internal part of the VBA code, such as this:

CODE
Sub ImportFile()
Dim fs As FileSystemObject
Dim f As TextStream

Set fs = CreateObject("Scripting.FileSystemObject")

strFileIn = "T:\strText.txt"

Set fin = fs.OpenTextFile(strFileIn)

Do While f.AtEndOfStream <> True

lin = f.ReadLine

If Trim(lin) Like "Billing Area:*" Then
strLine1 = Trim(Right((lin), InStr(":", 1), 15))
f.WriteLine strLine1
If Trim(lin) Like "Name:*" Then
strLine1 = Trim(Right((lin), InStr(":", 1), 25))
f.WriteLine strLine1
End If
End If

Loop

f.Close
Set f = Nothing
Set fs = Nothing
End sub

Public sub DoctorData()

NAME: ABERLE MD,K.L.
NUMERIC CODE: 31
MNEMONIC: KLA
DIVISION: ANESTHESIOLOGY
BILLING AREA(S): ANESTHESIA SVCS
BILLING AREA(S): SUPPLY
LOCATION(S): INPATIENT
LOCATION(S): OUTPATIENT
INDIVIDUAL BLUE SHIELD ID #: 00G49980
Individual Medicaid Id #: 00A664240
INDIVIDUAL MEDICARE ID #: 00G649980
STATE LICENSE NO.: G64998
VALID GROUP(S): 3,5
ALLOW AS PCP IN HMO(S): SANTA CRUZ MEDICAL CLINIC
ALLOW AS MEDICAL PRACTICE IN HMO: SANTA CRUZ MEDICAL CLINIC
ASSOCIATED HMO MEDICAL PRACTICE: SANTA CRUZ,MEDICAL CLINIC
UPIN NUMBER: A66424
NPI number: 1669447835
System Effective Date: 02/06/2008
WEBMD PROVIDER SPECIALTY CODE: 05
WebMD Provider Specialty Code: 05
Update Date: 02/06/2008
Update Time: 11:22AM
Update Username: MXF
Registration PCPn?: N
NAME: ABRAMS MD,R.C.
NUMERIC CODE: 228
MNEMONIC: RCA
DIVISION: FAMILY PRACTICE
BILLING AREA(S): FAMILY MEDICINE
BILLING AREA(S): FAMILY PRACTICE APTOS
BILLING AREA(S): URGENT CARE (MAIN)
BILLING AREA(S): URGENT CARE (SV)
BILLING AREA(S): URGENT CARE (WATSONVILLE)
BILLING AREA(S): URGENT CARE (WS)
BILLING AREA(S): LABORATORY
BILLING AREA(S): RADIOLOGY
BILLING AREA(S): SUPPLY
BILLING AREA(S): CARDIOPULMONARY
BILLING AREA(S): PATHOLOGY
LOCATION(S): APTOS
LOCATION(S): MAIN
LOCATION(S): SCOTTS VALLEY
LOCATION(S): WESTSIDE
LOCATION(S): WATSONVILLE
LOCATION(S): INPATIENT
LOCATION(S): NURSING HOME
LOCATION(S): OUTPATIENT
LOCATION(S): HOME
End Sub

Thanks,
Mike

 

[jedraw]

Where are you reading this data from?
Where are you planning on writing it to?
Please provide more details on why your company's network is preventing you from "authorized access" to the network and database.

Have you considered importing all of these records, which appear to be in XYZ.txt format, into a Temp table; then
parse the records and write/update/insert into appropriate
tables in the database?

There must be more to this....

 

[scriptscribe]

My company uses a "roaming profile" so that any employee can log in to any machine (it's a big company). Unfortunately, when I've tried to write code to open an existing Excel spreadsheet whether on a network drive or on my C: drive, I get a "not found" error.

I could create a temp table, but don't know how to go about populating it with these values.

I'm just cutting and pasting the data from Excel into the VBA module.

The system I'm writing to is IDX, which is a program used by hospitals and medical groups to process claims, referrals, store doctor and patient data, etc.

The code to write to IDX is not a problem, it's the reading of the data in the code.

Here's an example of the code that transmits to IDX:

Sub IDXWriteLine()
    On Error GoTo ErrorHandler

    Const NEVER_TIME_OUT = 0

    Dim BEL As String   
    Dim LF As String    
    Dim ESC As String   

    BEL = Chr(Reflection2.ControlCodes.rcBEL)
    LF = Chr(Reflection2.ControlCodes.rcLF)
    ESC = Chr(Reflection2.ControlCodes.rcESC)

    With Session
        .Transmit "13”
        .TransmitTerminalKey rcVtEnterKey
        .StatusBar = "Waiting for Prompt: Dictionary:   {"
        .WaitForString ESC & "[15D", NEVER_TIME_OUT, rcAllowKeystrokes
        .StatusBar = ""
        .Transmit "471"
        .TransmitTerminalKey rcVtEnterKey
    End With
    Exit Sub

ErrorHandler:
    Session.MsgBox Err.Description, vbExclamation + vbOKOnly
End Sub

 

[jedraw]

Instead of cutting and pasting Excel into Access, look at DoCmd.TransferSpreadsheet.

Look at

http://www.thescripts.com/forum/thread460341.html

message #2 etc.

 

[scriptscribe]

I'm not cutting and pasting into Access, I'm cutting and pasting into the VBA module behind the IDX Console screen. It's identical in every way to doing code behind forms and that sort of thing, except you're writing to an IDX screen instead of another office program.