Hello Chaps, I have the followin

[alibongo]

Hello Chaps,

I have the following problem:
(1) I have an ODBC link to a database which populates a spreadsheet via parameter and refesh buttons on a VB form.
(2) I then copy the populated section of the sheet to another sheet and then save it as a CSV. file.
(3)The point of doing this is that I am using a third party tool to create OLAP cubes from the CSV file. Unfortunatley the OLAP tool is not a data junction and I need to point to a specific file path.
(4)Therefore I had the idea that if I always call the csv file the same name and the code keeps checking if the file exists in a specific directory. i.e. it keeps looking to add the file to the directory until the cube processes the file and removes it from the directory. At the moment my VB keeps just copying over my old file.

I want it to look to see if it is there if it is keep looping until it has dissapeared and then copy the file to that location.

Public Const cSwitchFile As String = "Switch.csv"

'This sub is called from the refresh button which updates the query

Sub CopySwitchData()

Application.DisplayAlerts = False

strSheetName = cSwitchFile

If SheetExists(strSheetName) Then

Application.Wait (Now + TimeValue("0:00:10&quot)
'or Exit Sub, Loop ????????????????????????????

Else

Range("A4:F65000&quot.Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
ActiveWorkbook.SaveAs Filename:= _
cLandingPath & cSwitchFolder & cSwitchFile, FileFormat:= _
xlCSVMSDOS, CreateBackup:=False
ActiveWorkbook.Save
ActiveWindow.Close
Range("A1&quot.Select

End If

Application.DisplayAlerts = True

End Sub

Any ideas? I know this is very unelegant but it is just for a proof of concept. Cheers, Alistair

 

[ETID]

try this....


do until dir("drive:\path\"+strSheetName)=""
loop

 

[alibongo]

Thanks ETID,

I used that an put an application.wait to time the loop until the process has run within the OLAP tool. Thanks for your quick response.

Cheers, Ally