• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Facing issues with 'With WorksheetName.QueryTables.Add'

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,

Hope everyone is healthy and doing fine.
I have one macro. This macro is throwing bug at 'With WorksheetName.QueryTables.Add' this particular activity.

Code is something like below. My apologies, code is at another networks hence I can not copy it.

With WorksheetName.QueryTables.Add(Connection:="TEXT;" & MyPathFullName, Destination:=WorksheetName.Range("A1"))
some lines of code here

.Refresh Background Query:= False 'I am getting bug at this line, path not found.
End With

MyPathFullName is defined in a cell. This is combination of Folder name, file name and date.
User changes the date as per the requirement. So user might change it from 29 to 30 June.
So example of a path would be MyFolder1/06302020/MyFileName

Only requirement from the user side is that user has to make sure particular folder is present at the location.
So if I select date at 30 June, then a sub folder named as 06302020 must be present in the MyFolder1.

Problem start here, user enters date as 07012020 by mistake. Folder for 07012020 is not present.
Runs the macro, macro throws a bug, 07012020 folder not found.

User corrects mistake, enters date as 06302020. Folder is present.
Runs the macro, but still same bug, 06302020 folder not found.

In the macro pop-up we can see it mentions correct folder name which is 06302020.
Still issue is there, I checked the code, MyPathFullName mentions correct path name.

If we close and re-open the macro, enter date as 06302020 then macro runs perfectly.
Not able to understand the issue. Can anyone please help me in this.
 
This isn't my area of expertise, and without the actual code I see no way for me to be sure. (If I had the code on hand I could test it and try to confirm my hypothesis.) But what I suspect is a sequence something like this:

1) The use enters a bad date (one whose folder doesn't exist)
2) The QueryTables.Add method creates a query with the bad folder in it.
3) The error message is displayed.
4) The use corrects the folder name.

At this point you think the query is corrected. But if I'm right, the worksheet still has a query with the wrong folder in it. The Add method didn't replace the bad folder name, it created an additional query with the right one. Now there are two queries, and your program is still running the first one. If you close the worksheet (without saving it), the added queries are discarded and the new run works.

If you ask me how to confirm this hypothesis, I'm not sure what to tell you; I don't do enough with queries. But maybe you can use this to track down the problem.
 
Hi @BobBridges , thanks a lot for the help. My apologies for not uploading code. Am trying to.
You are correct, there must be two queries, that is why am getting bug.

If you get time, could you please tell me how do I rectify this two queries issue.
How do I discard one query and move on to next.

Have a nice day ahead. :)
 
Hi,

Please see below part of code for your reference.

Code: 
Sub Dummy_Code()
    MyWorksheet.Select
    MyWorksheet.Range("a1:A50") = "1"
   
    MyWorksheet.Range("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, OtherChar _
    :=":", FieldInfo:=Array(1, 2), TrailingMinusNumbers:=True
    MyWorksheet.Cells.Clear
   
    With MyWorksheet.QueryTables.Add(Connection:="TEXT;" & MyFileFullPath, Destination:=MyWorksheet.Range("A1"))
        .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(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
        2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False ' I get bug at this line
    End With
    Application.CommandBars("External Data").Visible = False
End Sub
 
Just in passing, I think the reason you get the error message at the last line is that the Refresh method actually executes the query that all the previous statements set up. But maybe you already understood that.

As for how to fix it, I'm thinking of two approaches:

1) The first way is more complicated to explain but will tell you more certainly what's going on inside VBA. You must make the same mistake your user made, that is, enter a non-existent folder, get the error, and then enter a corrected folder. At that point, if our hypothesis is correct, you have two queries in that worksheet's QueryTables collection. Pause the program and add MyWorksheet.QueryTables to your Watch list. Then expand it; I expect you'll see two items. I don't know how they're distinguished from each other. Maybe the query has a Name property; if so, the two queries will probably have different names (perhaps "Query1" and "Query1 (copy)"). Maybe they'll just be numbered. However they're labeled, you'll probably find that there's a Delete or Remove method available to the QueryTables collection that you can use to delete the invalid one. If you're not familiar with adding variables to the Watch list, let me know and we'll go into more detail.

2) But the longer-term solution is to prevent the error from happening in the first place. You cannot guarantee that users will never enter a wrong folder name, but your program can, and should, check the path to be sure it exists before attempting to create a query based upon it.

That's not hard. I would use the FileSystemObject to check the path. If you find the folder doesn't exist, your program can display an error message for the user, and never encounter the problem of the bad query because you intercepted the error before you created the query. If you've never used the FileSystemObject before, it's well worth learning about; it's very handy for checking on the existence of folders and files, and also for reading and writing files, checking on creation dates and several other things. You'll find some documentation for it at https://docs.microsoft.com/en-us/pr...developer/windows-scripting/hww8txat(v=vs.84).
 
ThrottleWorks, you said in your original post "If we close and re-open the macro, enter date as 06302020 then macro runs perfectly." I took you to mean that when you close and re-open the workbook, the problem of the extra query (if that's what's happening) goes away. Did I misunderstand?

Because if your problem goes away when you start over, then you don't need to find the hypothetical extra query; you just need to modify the program to spot the bad folder name and abend instead of accepting it. I don't mind explaining this better if you're not sure what I'm saying.
 
Hi @BobBridges , thanks for the help.
My problem is
'With MyWorksheet.QueryTables.Add(Connection:="TEXT;" & MyFileFullPath, Destination:=MyWorksheet.Range("A1"))'

If I check MyFileFullPath here, both the times it is correct. I mean, the path reflecting here is correctly.
I checked in Immediate Window, path is valid, but somehow invalid path is stored in SQL query.

And I am not able understand how do I skip query which consists invalid path. I am not able to differentiate query with invalid path from query with valid path.

Have a nice day ahead. :)
 
I'm confused. In your original post you said the problem starts when a user enters an invalid folder name ("...07012020 by mistake. Folder for 07012020 is not present"). Now you're saying "both times" the path is correct. Are we talking about two different things?

To avoid an error if the user enters an invalid path—and there is always that possibility—you have to detect the error before you add the query. I don't know how the user is entering the folder name, but let's pretend you're using InputBox and the user is typing just the last folder name. So your code might look like this:
Code: 
Const BasePath = "K:\Blah\Blah blah\Blah blah blah\"
Set ofs = CreateObject("Scripting.FileSystemObject")
If Not ofs.FolderExists(BasePath) then 'abend message; K:\Blah\Blah blah\Blah blah blah\ doesn't exist

' Let the user enter the last folder name.
LastFolder = InputBox("Enter the folder name:")
If Right(LastFolder, 1) <> "\" then LastFolder = LastFolder & "\"
MyFullPath = BasePath & LastFolder
If Not ofs.FolderExists(MyFullPath) then 'abend with a message that the user entered a bad folder name

' Now you know the folder exists; you can add and execute the query.
 
Hi @BobBridges really sorry for confusing and taking your time.

1) Partial path is present in a cell, let us say cell A1, example MyFolder1/
2) user types date in cell A2, for example 07102020
3) Select an Excel file name with a drop down, for example, MyFile.xls
4) Cell A3 is concat of A1,A2 and file name selected from drop down

And this is used as folder path in macro.
So in this case path will be MyFolder1/07102020/MyFile.xls

When macro runs, it tries to open the file MyFolder1/07102020/MyFile.xls
Highlighted part is important for me.
While typing date in cell A2, user has to make sure that a subfolder 07102020 is present in Folder1

Sometimes by mistake user types date as 07112020
And runs the macro
Now this folder is not present hence macro throws bug

So user goes back and type 07102020 in cell A2 and runs the macro again
But this time he gets same error MyFolder1/07112020/MyFile.xls is not present.
Actually user has corrected his mistake and running the macro again.
However he still gets the error.

So even if user is correct this time, SQL is referring to previous path while running the macro.
I am not able to resolve this issue.

Have a nice day ahead. :)
 
Ok, so what you need is to check the path before you add the query, pretty much like my code the last time but this time using the cells you described:
Code: 
Set ofs = CreateObject("Scripting.FileSystemObject")
PartialPath = MyWorksheet.Cells(1, 1).Value
If Right(PartialPath, 1) <> "\" then PartialPath = PartialPath & "\"
UserFolder = MyWorksheet.Cells(1, 2).Value
MyFullPath = PartialPath & UserFolder
If Not ofs.FolderExists(MyFullPath) then 'abend with a message that the user entered a bad folder name

' Now you know the folder exists; you can add and execute the query.
The point here is that you don't start the query add until you've confirmed that the folder the user entered does in fact exist. Then you won't run into this problem.
 
You must log in or register to reply here.
Back
Top