Excel cannot find the text file to refresh

[mcauliff]

Using MS Office 2003. I created a workbook that contains 3 sheet. Sheet 1 retrieves data from sheet 2 and 3.

Sheet 2 import data from an external csv file in the same directory as the workbook.

When the workbook is open, the Query Refresh dialog box is displayed. When the "Enable Automatic Refresh" button is click the error message is returned. Error message is; "excel cannot find the text file to refresh this external data range". I edited the Data Range Properties to contail the full path to the csv. The csv and workbook are in the same network server and directory.

I do not get the error, only other people trying to access the workbook

Are there security items that need to be set? What is causing this error?

 

[Gavona]

One thought: if you drag and drop a file from one folder to another then (on our network) it retains its original access permissions. If you copy and paste then the file ends up with the permissions relating to the new folder. Is it possible that the other users cannot access the text file as they don't have permission?



Gavin

 

[mcauliff]

The csv file is the output of an Oracle job. I could understand if the it was only the users, but the senior programmer is also unable to access.

On the properties tab of the file, I'm listed as the current owner. I believe that is the reason it is workign for me.

 

[Gavona]

Can you open the file and re-save it in order to get the appropriate access permissions for the folder.

Gavin

 

[SkipVought]

If you reference your Connection Path using a DRIVE, chances are that the other users have not used the same DRIVE notation.

I almost always, use VBA code to automatically adjust the Connection String to the Path of ThisWorkbook.

If you would like, I can supply the method I use.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mcauliff]

The open and re-save file didn't have any affect.

The path is: \\network_drive\Directory\filename. Didn't use a drive letter. example: \\networkdrivea\folder1\file1.csv

 

[SkipVought]

Your stated process has no effect on the Connection String that is stored in your query!!!

Open your workbook.

Select the sheet containing the IMPORT.

TURN ON YOUR MACRO RECORDER

Right-Click in the resultset and select Edit Query.

Click thru the wizard to FIND the file and return data to Excel.

TURN OFF your macro recorder.

Post your recorded code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mcauliff]

Hope I did this correctly. Would not the drive letter depend on where the user has the network share drive mapped on their individual PC?

Sub Debug_Error()
'
' Debug_Error Macro
' Macro recorded 1/6/2011 by bfhmem0
'

'
Range("A1").Select
With Selection.QueryTable
.Connection = "TEXT;O:\icims_jobs_extract.csv"
.TextFilePlatform = 437
.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)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[SkipVought]

put your worksheet name in there too....

Sub Debug_Error()
'
' Debug_Error Macro
' Macro recorded 1/6/2011 by bfhmem0
'

'
    
    With Worksheets("[b]YOURSHEETNAME[/b]").QueryTables(1)
        .Connection = "TEXT;" & ThisWorkbook.Path & "\icims_jobs_extract.csv"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mcauliff]

I'm assume that "YourSheeName" is replace by the sheetname where the data is imported. Mot on sheet1 where sheet2 is referenced via VLookups

I changed the name from Debug_Error

I also received an error message on saving; about a macro-free workbook. Saved the workbook as a xlsm type. The workbook was created using Office 2007. I have Office 2003


Still have the error message.

Do I need to do something with the "Edit Text Import" or "Data Range Properties"?

Sub ImportFile()
'
' ImportFile Macro
' Macro recorded 1/7/2011 by bfhmem0
'

'
    With Worksheets("Sheet2").QueryTables(1)
        .Connection = "TEXT;" & ThisWorkbook.Path & "\icims_jobs_extract.csv"
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[SkipVought]

The worksheet name is the name where the IMPORT querytable resides -- NOT the sheet referencing any other data.

So I assume that you are importing data into Sheet2.

Since you have 2003, save your workbook as an Excel 97-2003 Workbook

I tested this code and it performs an IMPORT.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]