excel: QueryTable... code problem?

[asilins]

VB isn't my area... I'm having trouble trying to get the QueryTable to actually populate the data into the active worksheet in Excel. It works in that it prompts me to navigate and select my data source file. The problem is once I select the data file source I get a "Run-Time error 1004" with the message that MS Excel cannot access the file "insert file path here" that I had just selected. What am I missing?

Here's the code:

Sub ImportAutoRead()
Dim fopen As Variant
fopen = Application.GetOpenFilename("Auto Read Files (*.imp), *.imp")

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & fopen _
, Destination:=Range("$A$2"))
.Name = fopen
.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 = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(9, 2, 2, 2, 9)
.TextFileFixedColumnWidths = Array(106, 12, 4, 6)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

End Sub

 

[SkipVought]

Can you do it manually, using Data > Import External Data...?

If you can, turn on your macro recorder and record importing.

Post back with your recorded code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[asilins]

Yes, I can run it manually and record it no problem but then it hard codes a source for the data file. We use over 50 different data files so the user needs to be able to select the file which is why I added in that portion to the begining of the code. Below is a copy of the code from recording a macro.


Sub Macro4()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;F:\AutoRead\BENNINGTON.IMP", Destination:=Range("$A$2"))
.Name = "BENNINGTON_9"
.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 = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(9, 2, 2, 2, 9)
.TextFileFixedColumnWidths = Array(106, 10, 4, 8)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[SkipVought]

Right away, I see that there are differences in the PARSING between your FIRST post and the LAST post.

.TextFileFixedColumnWidths = Array(106, 12, 4, 6)
.TextFileFixedColumnWidths = Array(106, 10, 4, 8)

Does the user add a new querytable each time? (Shoves existing queries to the right) Or is the intention to replace the data area with fresh import data?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[asilins]

The difference is me just running the marco recorder real quick and not clicking the identical column widths as previous. The intention is to fill the blank template with the portion of data that we need.

 

[SkipVought]

Your QT is already added. Just needs to have the Connection property assigned. Probably, all those .TextFile properties (that will never change) can be deleted from the refresh

You should explicitly define the ActiveSheet...

Sub ImportAutoRead()
    Dim fopen As Variant
    fopen = Application.GetOpenFilename("Auto Read Files (*.imp), *.imp")
    
    If fopen <> False Then
'[b]make sure that Data Range Properties NAME is BENNINGTON_9[/b]
        With [b]Sheets("YourSheetName")[/b].QueryTables("BENNINGTON_9")
            .Connection = "TEXT;" & fopen
            .Refresh BackgroundQuery:=False
        End With
    End If
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[asilins]

Thanks for your help Skip!

I see what you mean by defining the active sheet... But I can't hardcode in BENNINGTON_9 because that name will change depending upon the data file that the user selects at the beginning in fopen. The above code you posted doesn't specify the destination of the data within the active sheet.

 

[SkipVought]

I must have not been clear.

How many QT's do you intend to have?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[asilins]

Just the one QT will be run per data source page.

Steps for the macro

-User selects data source
-Macro fills the template with the appropriate columns of data
-Will then prompt for a location to SaveAs

 

[SkipVought]

You will only need ONE sheet with ONE QueryTable with ONE name, like "BENNINGTON_9" which is really irrelevant to ANYTHING except the procedure.

The user makes the file selection, the import occurs as merely a REFRESH, (not an ADD), then the Save As and then the user is ready to do it all over again. The existing data need not be deleted. In fact, deleting the data may delete the QueryTable, which you will not want to do.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[asilins]

Skip, Everything seems to be working. Thanks for your quick replies and knowledge. It made my day go a little smoother with this help.

 

[SkipVought]

Glad to hear.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}