VBA Script Needs to Locate File in CWD

[dac5039]

What my VBA script does is get external text data and store it in the first few columns. Then this data is used to create several graphs which are exported as .png images.

The problem is that I need to be able to pass my Perl, Fortran, and VBA scripts to another machine within a folder, have the Perl and Fortran code create the text file within that folder, and then have the .mxls file retrieve the text file and produce the graphs. So knowing the exact location of the text file won't be possible for other machines. So I tried simply using the VBA code for getting external text data with just the text file's location as the path name, hoping that VBA would look in its working directory by default. This apparently is not the case.

Thanks,

Dave

 

[SkipVought]

hi,

Dim sPath as string, sDB as string, sConn as string

sPath = ThisWorkbook.Path

sDB = "YourFileName"

sConn = "TEXT;" & sPath & "\" & sDB & ".txt"

for instance.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dac5039]

SkipVought, thanks for your input.

After inserting your code to test out, my script still cannot find the file..?

Sub read()
'read Macro
'Keyboard Shortcut: Ctrl+Shift+R
'
Dim sPath As String, sDB As String, sConn As String
sPath = ThisWorkbook.Path
sDB = "yearly_stats.txt"
sConn = "TEXT;" & sPath & "\" & sDB & ".txt"

    With ActiveSheet.QueryTables.Add(Connection:= _
        sConn, Destination:=Range("$A$1"))
        .Name = "yearly_stats"
        .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 = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(2, 5, 4, 5, 7)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[SkipVought]

do not replicate .txt

sDB = "yearly_stats[s].txt[/s]"
sConn = "TEXT;" & sPath & "\" & sDB & "[b].txt[/b]"

The reason I do not include the file extension in the DB name (file name) is that when you do connect strings for QUERIES rather than IMPORTS, it makes a difference.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]