Problem replacing/substituting a part of a String variable with a long text string

peejay

Board Regular
Joined
Jul 11, 2003
Messages
83
Hi
I have a macro which runs some SQL code to update a table on a server.
The code and SQL has run fine in the past, but I've hit a road block now, seemingly because SUBSTITUTE doesn't seem to like swapping in a really long string (~35k chars) for a short string into a STRING variable.

If anyone has a suggested work around, I'd be truly grateful.
Alternatively, if there's an easier way to upload data from an Excel sheet to an SQL table that would be even better to hear of.

So, this is my problem...

I'm writing data from the Excel file to the server table, and I store into a string variable ('SQL') a SQL code 'template' (ie a string with the SQL command set up, with a placeholder for the record values I want to upload) from a cell in my workbook which has a short replaceable text string in it '{Values}'.

I run a loop to build my list of values to upload (in a string variable called 'values').
I then use SUBSTITUTE to replace the {Values} string with the contents of the 'values' variable. Up until now, all has been good.

However, I have a situation where the length of the 'values' variable is ~ 35k chars long, and the SUBSTITUTE (and REPLACE) worksheet functions don't work (the {Values} string remains unchanged).

I've tried changing the variable to a VARIANT, and had swapped SUBSTITUTE with REPLACE, but no luck.

Here's the SQL template for reference:

/* Delete all existing records for the month... */
IF (SELECT COUNT(1)
FROM ProductionRevenue.dbo.tblAdjustments
WHERE MonthEnd = '2019-08-31') > 0
BEGIN
DELETE FROM ProductionRevenue.dbo.tblAdjustments
WHERE MonthEnd = '2019-08-31'
END

/* ...then INSERT new records from the Excel template, using VALUES created
by the macro */
INSERT INTO ProductionRevenue.dbo.tblAdjustments(
MonthEnd, RecordType, StaffCode, AssgnId, ClientCode, StaffGrade, ChargeCode, StaffProductName, ContractorType, StaffOUCode, NewOUCode, JobOUCode, ProdRevOUCode, StaffHours, StaffTargetAmt, StaffBudgetAmt, StaffAllocatedBudgetRev, StaffAllocatedProvisions, StaffAllocatedEAC, StaffAllocatedWriteOffs, StaffAllocatedNetRev
)
VALUES {Values}

Many thanks for your help in advance.
PeeJay
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top