.Refresh BackgroundQuery:=False HELP!!!

**mali90** · 12-09-2010, 03:48 AM

Hi...I'm relatively new to using VBA (about 6 months) and haven't quite come to grips with it yet. Basically, I've had a project handed over to me and so I've only written part of this code.

*AM USING EXCEL 2000 FOR THIS PROJECT*

A brief overview of what the program does:

Users fill in a userform, press OK, the program uses a querytable to pull some data from a given location and pastes it into a worksheet that uses a standard template. The problem is that the error is VERY random! For example, the program was working 100% fine, then suddenly it crashed and I got the message: "Run-time error '1004': Application-defined or object-defined error". Tried looking this up to see what the issue was, couldn't figure much out. Played around with filepaths and locations and all and suddenly it started working again. Was using it yesterday and it was okay, then suddenly out of nowhere it crashed again with the same message!

The code is:

'Create query table to read in data from CMM .dat files to "Import" Sheet
    
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & Filepath & import_file_name, Destination:=Range("A" & Row_Count))
        .Name = import_file_name
        .FieldNames = True
        .RowNumbers = True
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
'        .TextFilePlatform = -536
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1)
'        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False  <---THIS IS WHAT KEEPS GETTING HIGHLIGHTED AS THE ERROR
    End With
    
    import_file_name = Dir
    
     Run_Count = Run_Count + 1
    
    Loop

I am now completely stuck and have no clue what to do, PLEASE HELP!!!

**romperstomper** · 12-09-2010, 04:00 AM

Are you sure the source file exists and is not in use?

Note: when posting code on the forum, please use code tags in accordance with the rules. I have added them for you on this occasion.

**mali90** · 12-09-2010, 04:08 AM

What do you mean by 'in use'? Do you mean in use whilst the program is trying to run? The source file definitely exists-this error happens randomly as far as I can tell-there have been no notable differences between when it was working and when the error occurred.

Thanks for your help in advance.

**romperstomper** · 12-09-2010, 04:46 AM

Originally Posted by mali90

What do you mean by 'in use'? Do you mean in use whilst the program is trying to run?

Yes, I do.

**mali90** · 12-09-2010, 05:04 AM

Almost 100% of the time it is not being used whilst the program is trying to run, but to be honest I can't recall all the times because I've used the tool so many times without an error that when the error does come up, I can't recall for sure whether it was being used or not. What do you suggest I do?

**romperstomper** · 12-09-2010, 06:48 AM

It depends - what do you want to do in response? Ignore the error and move on, try again, something else?
When the error occurs, if you run the code again, does it work, or does it always fail thereafter?

**mali90** · 12-09-2010, 09:03 AM

Ideally I'd like to find out why this error message randomly appears and then either try to eliminate it or find a way to work around it and make sure the program still works. I'm really struggling so if there's anything you can suggest or if you need to see more of the code to help, please let me know. Many thanks again.

**romperstomper** · 12-09-2010, 09:15 AM

Since the code works almost all the time, I don't think seeing more code will help. You will need to work out why the code is failing when it does - I still think the most likely cause is that the source file is in use by some other process, but then I don't know your work processes.