PDA

View Full Version : Solved: Automate data import



alstubna
03-13-2008, 10:39 PM
Hello All!

I'm trying to automate a data import.

I recorded this macro (I'll only show a few lines of the code that are relavent):

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\X\XX\Lists\car parts.txt",

What I'd like to do is to be able to select which file I import. I thought I might be able to figure it out by recoding the steps and then modifying the code but I'm lost.

So, I basically need to be able to open the directory C:\X\XX\Lists and then select which file to import.

I'm sure this is fairly easy to do but I'm not at all versed in VBA.

Appreciate any assistance you can offer.

Al Stubna

BreakfastGuy
03-13-2008, 11:08 PM
Try this:

Sub RetrieveFileName()
Dim sFileName As String
'Show the open dialog and pass the selected _
'file name to the String variable "sFileName"

sFileName = Application.GetOpenFilename
'They have cancelled.
If sFileName = "False" Then Exit Sub
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;& sfilename)
End Sub

Bob Phillips
03-14-2008, 02:03 AM
With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = "C:\test"
.AllowMultiSelect = False

If .Show = -1 Then

MsgBox .SelectedItems(1)
End If
End With

alstubna
03-14-2008, 07:39 PM
Ok, here's what I've come up with but I get a run time error. The fileopen dialog opens to the correct directory, I select a file then click the Open button then I get the run time error:

Sub RetrieveFileName()

Dim sFileName As String

ChDir "C:\OPS\DIST 2008\PLists"
sFileName = Application.GetOpenFilename

If sFileName = "False" Then Exit Sub
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\OPS\DIST 2008\PLists\" & sFileName, Destination _
:=Range("A2"))
.Name = sFileName
.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 = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Here's the error I get:

Run-time error 1004
The file could not be accessed. Try one of the following:
Make sure the specified folder exists
Make sure the folder that contains the file is not read-only
Make sure the file name does not contain any of the following: blah, blah
Make sure the file/path name doesn't contain more than 218 (characters?)

And this line of code is highlighted:

.Refresh BackgroundQuery:=FALSE

None of the conditions in the error message apply.

Thanks for any assistance you can offer.

Al Stubna

alstubna
03-15-2008, 10:45 PM
I managed to get this working but I had to change the way I did it. It's a real kludge!

I pulled the file names into the sheet then setup a drop down list with the file names as selections. Then the data import is based on the selection of the drop down list.

Thanks to those who assisted.

Al Stubna

Bob Phillips
03-16-2008, 02:14 AM
Why, what is wrong with our suggestions. That is the sort of thing we use in production apps.

alstubna
03-16-2008, 08:46 PM
Why, what is wrong with our suggestions.

Well, they didn't work. Not sure why.

What I ended up with is basically the same thing "BreakfastGuy" suggested but I'm using cells to hold the file name. When I used the variable in the code I kept getting run-time 1004 errors. I replaced the variable with a cell reference and it works.