Import another spreadsheet with VBA

[Mykiej23]

Hi,

I have some vba code that a former colleague created and said there wasnt a way for us to change the code to stop it asking for the file name. Its a static file that we download each day and overwrite the previous one. However, each day we need to navigate to the folder to select it.

The code is as follows:

Sub Import()


Dim ws As Worksheet


Call ClearTextToColumns
On Error GoTo errorHandler
Set ws = ActiveWorkbook.Sheets("Sysr_1_Raw Data")
strFile = Application.GetOpenFilename("CSV Files(*.csv),*.csv", , "Please select csv file...")
With ws.QueryTables.Add(Connection:="TEXT;" & strFile, _
Destination:=ws.Range("A1"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh
End With
Exit Sub
errorHandler:
MsgBox "Data import cancelled.", vbCritical, "Now Recon Import"
errorState = True
End Sub


Is there an easy way to change this without changing the entire code to look only at one file path?

Thanks,
Michaela

 

[mumps]

Try this macro. Change the path and file name (in red) to suit your needs.

Sub Import()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, wbOpen As Workbook
    Call ClearTextToColumns
    On Error GoTo errorHandler
    Set ws = ActiveWorkbook.Sheets("Sysr_1_Raw Data")
    Set wbOpen = Workbooks.Open("[COLOR="#FF0000"]C:\Test\Sample.csv[/COLOR]")
    With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh
    End With
    Application.ScreenUpdating = True
    Exit Sub
errorHandler:
    MsgBox "Data import cancelled.", vbCritical, "Now Recon Import"
    errorState = True
    Application.ScreenUpdating = True
End Sub

 

[Mykiej23]

Try this macro. Change the path and file name (in red) to suit your needs.

Sub Import()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, wbOpen As Workbook
    Call ClearTextToColumns
    On Error GoTo errorHandler
    Set ws = ActiveWorkbook.Sheets("Sysr_1_Raw Data")
    Set wbOpen = Workbooks.Open("[COLOR=#FF0000]C:\Test\Sample.csv[/COLOR]")
    With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh
    End With
    Application.ScreenUpdating = True
    Exit Sub
errorHandler:
    MsgBox "Data import cancelled.", vbCritical, "Now Recon Import"
    errorState = True
    Application.ScreenUpdating = True
End Sub

Thank you!

 

[mumps]

You are very welcome.