Data Connections (External Data Proerties) - Remove "clear unused cells"

[Kajeana]

I have a template with formulas in column I and J. When the user makes a data connection inside the template, it brings data into columns A thru H. My formulas are being cleared out in the cells below the row brought in. Example they make a connection to an excel file that has 200 rows. When the connection is made, my formulas in columns I and J from row 201 through 4000 get cleared/deleted. I found the External Data Properties dialog box and it gives you one of three choices and they all clear or delete unused cells. Is this what is clearing/deleting my formulas? Any way to stop this from happening in the code? The users need to refresh from time to time and it will add more rows of data to the existing or might change some of the existing row data. I need the formulas to stay in where I have them. How do I stop Data Connections from deleting them? Thank you, Kajeana

 

[Fazza]

hello, Kajeana

Often with external queries there are formulas - these will auto-fill if you set the query's auto-fill adjacent formulas property to true. I don't know if this helps you. Such as you have headers in row 1 and data (pulled in with the query) from rows 2 to 200, and formulas in columns I:J. Refreshing of the query pulls in additional data - so last time was 199 data rows and the refresh pulls in additional rows 201:4000. By setting the fill down adjacent formulas, the formulas in I2:J200 are extended down to row 4000. Though maybe this is not what you want?

If you want something different, maybe VBA (program code) is best. By using VBA you can do just about anything. OK? So, do the refresh and VBA create the formulas in the correct range. A variation of this might be to not use the built-in (worksheet) external data query but instead (in VBA) use ADO or similar to return a recordset, then paste that to the worksheet (and then populate the formulas in I:J).

OK?

 

[Kajeana]

This seems like it would work if the formulas are coming in, but they are not. They are already inside the template that is getting the data imported to it. Nothing is being imported to columns I through J. The formulas are already there. When I use the Data Connections box to set the link, it is erasing the formulas that were already entered into the template so I don't think I need it to auto-fill. How do I stop the Data Connections from doing that? I really don't want the user to have to copy it down each time they do a refresh. I would record a macro to fill in the formulas for them but recording macros doesn't like IF statements to be entered, I get errors. I have the ability to edit VBA, but not to write it from scratch. If there is code in the VBA window for using Data Connections, I am not sure how to access it, would it be a module in VBA?

 

[Fazza]

As the formulas are not like I described in my first paragraph, the approach in my second paragraph applies. It should be straightforward to code, including any IF statements. Please give some details.

The code might be like
range("I201:J4000").formular1c1 = "=IF(xxx, yyy, zzz)"

 

[Kajeana]

Overview of sheet usage: The user opens the templete, I and J already have the formulas set up in them. Sheet 1 is named Epicor Export and Sheet 2 is Service Stock. The user sets up a data connection to a Engineering BOM (Bill of Materials) excel file they downloaded from Epicor ( a database ). The BOM data goes into columns A thru J and uses as many rows needed. Problem is Data Connections clears out my formulas in the rows not being used. Then the user runs a macro I set up that copies the data brought into to Sheet 2 (Service Stock). The user then has manual entries in a few columns in sheet 2. When the Engineer updates his BOM, the user exports updated excel sheet and overrids the existing one they had. Then they open the template they created and do a refresh. The formulas and conditional formatting shows them what has been updated in the BOM. when the do the refresh, it could add some rows of data. So yes that sounds like what I need. Some VBA code to automate entering the formulas each time they do a refresh for the update data connection. Column I and J have different formulas. And I would have to have it put into all the cells in those columns from 1:4000, only because I never know how many rows the first input will bring in. My formula in column I is =IFERROR((VLOOKUP([@[Part Number]],'Service Stock'!D:F,1,FALSE)), "NOT on Service List") and column J has =IF([@[Compare Service]]="Not on Service List",[@[Compare Service]],""). In column J I also have conditional formatting to turn the cell pink if something changed in the refresh. I went into VBA and inserted a new module. I used your code and put my formulas in it. But I am getting a compile error that it is not set up correctly. This is what I put, Range("I201:J4000").FormulaR1C1 = "=IFERROR((VLOOKUP([@[Part Number]],'Service Stock'!D:F,1,FALSE)), "NOT on Service List"))" range("I201:J4000").formular1c1 = "=IF([@[Compare Service]]="Not on Service List",[@[Compare Service]],""))" So what I have is incorrect, am I missing something or do I have it entered incorrectly, also can I add the conditional formatting to column J? I wish there was a way to just turn off the clear cells when the data connection is made. But Thank you so much for your help, much appreciated.

 

[Fazza]

Hi, it looks like just the quotes need to be doubled up. so instead of

"=IFERROR((VLOOKUP([@[Part Number]],'Service Stock'!D:F,1,FALSE)), "NOT on Service List"))"
try
.formula "=IFERROR((VLOOKUP([@[Part Number]],'Service Stock'!D:F,1,FALSE)), ""NOT on Service List""))"

 

[Kajeana]

Getting closer. I have this in module 1
Sub formulas()
Range("I1:J4000").FormulaR1C1 = "=IFERROR((VLOOKUP([@[Part Number]],'Service Stock'!D:F,1,FALSE)), ""NOT on Service List""))"
Range("I1:J4000").FormulaR1C1 = "IF([@[Compare Service]]=""Not on Service List"",[@[Compare Service]],""))"
End Sub
and I am getting error - Run-time error '1004' Application-defined or object-defined error. Any ideas on what I have wrong? Thanks, Kajeana

 

[Fazza]

First off, do the formulas work OK in the worksheet? Exactly what are the successful formulas?

Or, do these simpler (different) formulas work? If so, can you modify to the formulas wanted?

Sub formulas()
Range("I1:I4000").Formula = "=IFERROR(1/0, ""NOT on Service List"")"
Range("J1:J4000").Formula = "=IF(1=2,3,"""")"
End Sub

 

[Kajeana]

The formulas are working ok. I used your code and it worked but only copied to row 786? Not sure why that was. I thought maybe something was in a cell, but I cleared it and it wasn't. Which led me to think maybe entering text in row 4000 would cause the formulas to stay. So I tried that. It worked with the input file that was set up in the data connection, but when trying it the template out with another raw input file, it didn't work. So as it goes, I ended up recording a macro that copied the formulas from row 2 to 4000. What I am going to have the user do is run that macro right before they do a refresh. Then when they do the data connection refresh, it erases the formulas not used. It will work as long as they don't forget to run the macro. I guess in this case one thing lead to another and I ended up with something I could use. Thank you very much for hanging in there this week with all your advice. Very much appreciated!

 

[Fazza]

well done