PDA

View Full Version : Relative Paths within Excel for CSV imports



farmdwg
02-15-2017, 01:10 PM
I'm hoping this can be done as what I've been finding so far is that Excel does not support relative paths for workbook connections. What we have here is an Excel workbook that auto-updates the worksheets from CSV files. These pull in the data from a hard coded folder (c:\temp\premiumreports\name_of_CSV_file.csv). This method forces me to put everything into c:\temp\premiumreports everytime or if I send the file to a customer they have to create the same directory structure. What I would like to do is place my xlsx file into any folder (c:\report or the user's desktop) with the necessary CSV files and when I open the xlsx the data is imported automatically. Is this possible?

The screenshot below is one of the 12 or so data connections that I have that imports the data onto it's own sheet.

18384

I have tried this suggestion, but it errors out with a 1004 error.


Sub refreshMsgConnection() Dim csvFileName As String
csvFileName = "msg_by_weeks.csv"


Dim filePath As String
filePath = ActiveWorkbook.Path


Dim conString As String
conString = "TEXT;" & filePath & "\" & csvFileName


With ActiveWorkbook.Connections("msg_by_weeks").Ranges.Item(1).QueryTable
.Connection = conString
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.TextFilePromptOnRefresh = False
.Refresh BackgroundQuery:=False
End With
End Sub

Kenneth Hobs
02-15-2017, 04:09 PM
Welcome to the forum!

I could not duplicate that error.

What ever method you use, you should probably check that the csv file exists.

Example desktop subfolder:

Sub Ken()
Dim p$
p = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\CSV\"
On Error Resume Next
MkDir p
On Error GoTo 0

Shell "explorer " & """" & p & """", vbNormalFocus
End Sub

You might also look into the query. Here is one method. http://vbaexpress.com/forum/showthread.php?p=162725#post162725