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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.