Prev: NumberFormat not getting changing onFly changing the regional sett
Next: Automation of Excel Report using Access Data
From: Dave Peterson on 28 May 2010 11:56
Did the error occur on the .name line? Maybe you're trying to use a QT name
that's already in use???

If you actually type out the string in that .add() portion, does the code work?

with activesheet.querytables.add(connection:="TEXT;C:\yourpath\yourfilename",...

Does it work? Maybe your variables aren't what they think they are???



NoSpam(a)aol.com wrote:
>
> Thanks so much for your reply. I am the developer and I am trying to open
> a file which does exist.
>
> The code has worked before and I have no idea why it has stopped working.
> I have just added a LOT of code to portions of a .xla which SHOULD have no
> effect on this portion. Obviously, I accidentially change something in
> relevent code, or something in code I intended to change is having an
> unanticipated side effect.
>
> The code that is failing is:
> With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & Path & "\" &
> FileName, _
> Destination:=Range("A2"))
> .Name = "SCCI-S10"
> '===Removing the code between these comments does not prevent the error
> .FieldNames = True
> .RowNumbers = False
> .FillAdjacentFormulas = False
> .PreserveFormatting = True
> .RefreshOnFileOpen = False
> .RefreshStyle = xlInsertDeleteCells
> .SavePassword = False
> .SaveData = True
> .AdjustColumnWidth = True
> .RefreshPeriod = 0
> .TextFilePromptOnRefresh = False
> .TextFilePlatform = 1254
> .TextFileStartRow = 1
> .TextFileParseType = xlDelimited
> .TextFileTextQualifier = xlTextQualifierDoubleQuote
> .TextFileConsecutiveDelimiter = False
> .TextFileTabDelimiter = False
> .TextFileSemicolonDelimiter = False
> .TextFileCommaDelimiter = True
> .TextFileSpaceDelimiter = False
> .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
> 1, 1, 1, 1, 1)
> .TextFileTrailingMinusNumbers = True
> '===Removing the code between these comments does not prevent the error
> .Refresh BackgroundQuery:=False
> End With
>
> Thanks for any additional clues
>
> On Fri, 28 May 2010 08:48:27 -0500, Dave Peterson
> <petersod(a)verizonXSPAM.net> wrote:
>
> >This is a common error in excel macros that aren't behaving as intended.
> >
> >It's almost a catchall error code for lots and lots of different problems.
> >
> >If this error only occurs when a single workbook is open, then there's a good
> >chance that the error is in the code in that workbook's project.
> >
> >If you're not the developer of the macro, then go back to that developer and
> >explain what's happening.
> >
> >If the error occurs at various times and you can't really isolate the problem,
> >then you'll have some detective work to do.
> >
> >Chip Pearson has some notes on how to diagnose errors:
> >http://www.cpearson.com/excel/StartupErrors.htm
> >
> >And Jan Karel Pieterse has more notes:
> >http://www.jkp-ads.com/Articles/StartupProblems.asp
> >
> >Essentially, you'll turn off all the addins/startup workbooks and then open
> >excel. And then turn on each addin (closing and reopening) one at a time to
> >find the culprit.
> >
> >NoSpam(a)aol.com wrote:
> >>
> >> I am getting this error but the error is not arising in any application or
> >> object I created so it must be coming from Excel, VBA, or other MS writtern
> >> code. Is there any way to find out specifically what the error means? The
> >> VBA Error(Err) function is returning this error.
> >>
> >> Thanks.

--

Dave Peterson
From: Dave Peterson on 28 May 2010 11:58
Is the code in a General module -- or is it in a worksheet module.

If it's in a worksheet module, then this unqualified range (Range("A1")) refers
to the sheet that owns the code--not the activesheet.

Try qualifying the range:
...Destination:=activesheet.Range("A2"))...



NoSpam(a)aol.com wrote:
>
> Oops - I should have mentioned that the worksheet was just created, so
> there are no already existing queries.

--

Dave Peterson
From: Dave Peterson on 28 May 2010 11:59
I'd still keep an eye out for problems.

(But I've had the same thing happen to me. I don't usually blame excel -- I
just figured that I made a change that I thought was insignificant and it
wasn't!)

NoSpam(a)aol.com wrote:
>
> Weird - I commented out some code and the problem went away. Then I
> started restoring the code in sections until, testing each time.
>
> Finally I uncommented all the code and the error was still gone even though
> the restored code that worked was 100 % identical to the code that
> previously failed.
>
> On Fri, 28 May 2010 10:47:04 -0400, NoSpam(a)aol.com wrote:
>
> >Oops - I should have mentioned that the worksheet was just created, so
> >there are no already existing queries.

--

Dave Peterson
First  |  Prev  | 
Pages: 1 2
Prev: NumberFormat not getting changing onFly changing the regional sett
Next: Automation of Excel Report using Access Data