vba select location of .csv file then run import to excel

[lisagippe]

Hi,

I've got code written to import a csv file to a worksheet, the csv file will always be housed in the same folder on our server, the problem is everyone running the code has that paticular server named something different ie X:\Shared or G:\Shared or I:\Shared. I have a code that allows you to choose a file to open and the code to import the csv file from the root directory, problem is I'm not sure how to marry the two. I need the open file dialogue box to open, the user chooses the csv file, then the import continues as normal.

Open File Code:
Sub Openfile()
Dim OFile As String

OFile = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file")
If OFile = False Then
' They pressed Cancel
MsgBox "You must choose the Accnt Detail CSV file to continue the breakout"
Exit Sub
Else
Workbooks.Open Filename:=OFile
End If
End Sub


Import code:
With ActiveSheet.QueryTables.ADD(Connection:= _
"TEXT;H:\Shared\SI\6K\Sales Incentive Query_6k_CSV.csv", _
Destination:=Range("$A$1"))
.Name = "Sales Incentive Query_Local Sales_6k_CSV"
.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 = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

 

[SkipVought]

Hi,

ADD you connection ONE TIME.

Change the connection string to your server\share, as

 With [b]UseSpecificSheetObject[/b].QueryTables(1)
   .Connection = "TEXT;[b]\\YourServer\YourShare[/b]\Shared\SI\6K\Sales Incentive Query_6k_CSV.csv"
   .TextFilePromptOnRefresh = [b]True[/b]
   .Refresh BackgroundQuery:=False
  End With

Then each time you REFRESH, you will be prompted for a new file name.

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

 

[lisagippe]

Excellent, but to throw a wrench in it, the macro as a whole adds Accnt Details2 as a sheet, then creates the import and imports everything, then after copy/pasting to the appropriate sheets deletes the Accnt Details2 sheet. So there is no refreshing of the connection only building of it everytime it is run.

 

[SkipVought]

Why do you have to ADD & DELETE the Accnt Details2 sheet?

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

 

[lisagippe]

I add and delete it because once the csv is imported it takes the original file from 3 megs to 18 and not everyone's pc here is the same. I have more ram and can run larger files but it just locks up for the rest of them. So to keep the original file running smoothly and not breaking in the future(we run it about 20 times per quarter) I wanted to keep the org file as small as possible.

But it just ran great on my pc and other people's as is, so yay!! Thank you skip you're always a huge help!

 

[SkipVought]

You could always keep your workbook as a sort of template and spawn the user's workbook, sans source data.

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

 

[lisagippe]

True, but with a different person of the team running them every quarter or even multiple people and different amounts of data everytime wouldn't that cause some data compromise at some point?

 

[SkipVought]

Can't say for sure because I don't know your process.

But a well designed process should be able to be run by anyone with authorization, from anywhere in your network.

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