CSV import using a user specific file name?

[thommo41]

Hi,

I have a routine that imports a CSV file, based on a user specified file name (project number). It works, but I have a few modifications that I need to do...

1. It MUST account for user error on entering the file name. How can I check to see if the file exists OR break out of the routine on error with a messagebox???

2. The above would be great to know, and would be enough of a solution for me to implement it. BUT if this file name (which is a project number) could be passed through to excel from a web page (a button or something?) then that would be much better. Is this possible? Could the filename (the project number part of it) be passed through via a web page or something?

Code is below, many thanks for any suggestions.

Sub Acsvimport()
Dim Message, Title, Default, MyValue, infile
Message = "Enter the MG project number"    ' Set prompt.
Title = "Input Project Number"    ' Set title.
Default = "MGxxxx"    ' Set default.
' Display message, title, and default value.
MyValue = InputBox(Message, Title, Default)
infile = "H:\EXCEL\projcost\" & MyValue & "-costs.txt"
MsgBox (infile)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
' Reads in the CSV file
    Sheets("import").Select
    Cells.Select
    Selection.Delete Shift:=xlUp
    Range("a1").Select
    Application.DisplayAlerts = True
    
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & infile, _
        Destination:=Range("A1"))
        .Name = "projcosts"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlWindows
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = "|"
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1)
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[njimack]

Take a look at http://j-walk.com/ss/excel/tips/tip54.htm for functions to determine whether a file exists.

 

[thommo41]

Thanks for the link, I assume you mean this bit...

Private Function FileExists(fname) As Boolean
'   Returns TRUE if the file exists
    Dim x As String
    x = Dir(fname)
    If x <> "" Then FileExists = True _
        Else FileExists = False
End Function

Looks very simple, I am sorry to ask such a silly question, but how do I implement this within my code/routine???

Many thanks
Alan

 

[njimack]

Here's an extract from some code I use...

Option Explicit

Public FileLocation As String
Public OpeningFileName As String
Public PathExists As String
Public FileExists As String

Private Function Path() As Boolean
'   Returns TRUE if the path exists
    Dim x As String
    On Error Resume Next
    x = GetAttr(FileLocation) And 0
    If Err = 0 Then PathExists = True _
       Else PathExists = False

End Function

Private Function File() As Boolean
'   Returns TRUE if the file exists
    Dim x As String
    x = Dir(OpeningFileName)
    If x <> "" Then FileExists = True _
       Else FileExists = False
End Function


Sub Update()
    Dim Filename As String

    'Change this to the correct path - make sure you leave the forward slash (\) at the end
    FileLocation = "s:\assetall\main\liberata\"

    'Need to ensure the user saves the file using this file name
    OpeningFileName = "BAMVAL.exp"
    OpeningFileName = FileLocation & OpeningFileName

    'Ensure Path and File exist
    Call Path
    If PathExists = False Then
        Call MsgBox(FileLocation & " doesn't exist", vbOKOnly + vbCritical, "Process terminated")
        Exit Sub
    End If

    Call File
    If FileExists = False Then
        Call MsgBox(OpeningFileName & " doesn't exist", vbOKOnly + vbCritical, "Process terminated")
        Exit Sub
    End If
.
.
.

 

[thommo41]

I've got it working, as far as it will compile, but it doesn't work. It returns FALSE, even if file does exist?

 

[thommo41]

I have got it to work, but it stops my other routines from working. I guess it's to do with the "Public FileLocation As String" declarations, without those, obviously the file check doesn't work, but the rest of my code continues to work.

This is VERY frustrating Anybody feel sorry for me and want a look