How to continually connect to an Excel sheet that gets overwritten by another process/user

[hvnsmeet]

Hello everybody!

I guess the subject line is kind of confusing, so I will try and explain:

• I have a server where I have a timed subscription that in every x minutes it will send a report (Excel format) to a path on my network drive. Let's call this path '\\server1\andrew\'
  I have it set up so that the server overwrites the old versions of my reports with new versions.

• I have an Excel Workbook where I use data connections to import the reports from the aforementioned server. Ideally, I wanted this workbook to perpetually refresh or even manually refresh with the the newest version of the reports.

Now, my connection looks like:

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=\\server1\andrew\reportTest.xls;Mode=Share Deny None;Extended Properties="HDR=YES;";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False

​

I get no problems finding the files, so that's fine...But when the interval comes for the server to update it, I will get an alert from the server saying that it failed to write over the old file because it is in use by another process (I imagine that process being the data connection). Because I believe that I am perpetually connected and therefore using the Excel file that it wants to replace, it fails.
Now, I am using this workbook specifically so I can click on a button on one sheet so it goes to sheet 2 or sheet 3 (direct link to reports1.xls and report2.xls) and sorts and filters it. I have no intention to save over it. I am unsure of how to allow the server to overwrite these two xls files despite me having a data connection to it. I tried messing with the Modes but I don't know if that will exactly work. Any help is appreciated.

Thanks,

Andrew

 

[pbornemeier]

The way I handle something similar is to
1) delete any query objects on the worksheet (this is usually not needed, but it is there in case of a bad query in a previous session
2) Delete all data on the worksheet
3) Run the query
4) delete the query object
5) Process the new data

this way I am not tied to the source, but for a few seconds in the cycle during each pull.

You could check the date-time stamp of your source file every few minutes (\\server1\andrew\reportTest.xls) and automatically run the above steps when it changes from the previous setting.

Function GetDateLastModified(strFullyQualfifiedFilePathAndName As String)
    On Error Resume Next
    Dim objFS As Object
    
    'This creates an instance of the MS Scripting Runtime FileSystemObject class
    Set objFS = CreateObject("Scripting.FileSystemObject")
    
    GetDateLastModified = Format(objFS.GetFile(strFullyQualfifiedFilePathAndName).DateLastModified, "YYYYMMDD HH:MM:SS")
    
    Set objFS = Nothing

End Function

 

[hvnsmeet]

Sorry, I'm a little new to VBA... Let me see if I have this straight...

I established my workbook connections through the wizard instead of VBA. By deleting the query objects, do you mean I just should delete these workbook connections? What strikes strange to me is that there is an option in the data definition tab to 'always use connection file' which I unchecked. I thought that through this action I could manually refresh my data and then the connection to the file would release when it's done. Apparently, this is not the case so I am confused about that.

Anyways, so then by 'run the query' I would have to record a macro to go back to the wizard (since I don't know how to establish a connection in VBA) and make a connection and then assign that to all of my buttons [once I click on a button, it goes to the report and filters/sorts it by a criteria; I will just put the data connection creation script prior to all of this] and then also assign at the end of it a macro/script to delete the connection.

Is there a way where I can set my external data properties as well since I would like it to overwrite existing cells w/new data and only preserve cell formatting?

Sorry for all of these questions! Again, I am new to this and forums in general so I hope I'm not being too greedy asking all of these questions.

 

[pbornemeier]

I definitely do not have a full understanding in the use of the QueryTables, but the following code shows how I am using it. There is probably many ways to do it more elegantly. Much of this code was generated by turning on the macro recorder while using the query wizard (Data | Import External Data | New Database Query | Excel Files | OK | (select a file) | OK | (ensure Options | System Tables is checked) (select worksheet/column(s))) and pulling data from another Excel file.

You can edit out the nested arrays and convert the connection string to s simple (albeit long) string, although I have not done it with this particular query. The back-single-quote (unshifted tilde key) is required to delimit worksheet names

It looks as if you are pulling from an Excel file, I am not sure why your code uses Provider=Microsoft.ACE.OLEDB.12.0 . If you use the Excel option (steps outlined above) you have more control on the data transfer from an Excel file to an Excel file.

Check out the options for .RefreshStyle if you choose overwrite, I would recommend deleting the current data before importing new. If your first import had 200 rows and you next import had 150, then the last 50 from the first import would still be there.

     Dim strResults As String
     Dim intImportCount As Integer
     Dim iX As Integer
     
     Worksheets("NN").Select
     
     For iX = ActiveSheet.QueryTables.Count To 1 Step -1
         ActiveSheet.QueryTables(iX).Delete
     Next
     Range("A1").CurrentRegion.Cells.Clear
     Range("A1").Select
     With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
         "ODBC;DSN=Excel Files;DBQ=Z:\METRICS (LIMDIS)\LR Assessment Metrics\PMC Data\NN LR Data.xls;DefaultDir=Z:\METRICS (LIMDIS)\LR Assessm" _
         ), Array("ent Metrics\PMC Data;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;") _
         ), Destination:=Range("A1"))
         .CommandText = Array( _
         "SELECT `'PMC Data$'`.Proj, `'PMC Data$'`.Proj_Title, `'PMC Data$'`.Type, " & _
         "`'PMC Data$'`.Hull, `'PMC Data$'`.Tpav, `'PMC Data$'`.AQWPlwk, `'PMC Data$'`.OTlwk, " & _
         "`'PMC Data$'`.Start, `'PMC Data$'`.End, `'P" _
         , _
         "MC Data$'`.CP, `'PMC Data$'`.BQWSlwk, `'PMC Data$'`.STlwk, `'PMC Data$'`.DataDt, " & _
         "`'PMC Data$'`.BQWP, `'PMC Data$'`.AllowCW, `'PMC Data$'`.BQWSNXT, `'PMC Data$'`.BQWSCUR" & _
         Chr(13) & "" & Chr(10) & "FROM `'PMC Data$'` `'PMC Data$" _
         , "'`")
         .Name = "Query from Excel Files"
         .FieldNames = True
         .RowNumbers = False
         .FillAdjacentFormulas = False
         .PreserveFormatting = True
         .RefreshOnFileOpen = False
         .BackgroundQuery = True
         .RefreshStyle = xlInsertDeleteCells
         .SavePassword = False
         .SaveData = True
         .AdjustColumnWidth = True
         .RefreshPeriod = 0
         .PreserveColumnInfo = True
         .Refresh BackgroundQuery:=False
     End With
     Application.CommandBars("External Data").Visible = False
     Range("A1").Select

The forums are here to provide answers to questions. If you do a google search with Query Tables site:mrexcel.com - you will get a lot more info on using them.