Link to home
Start Free TrialLog in
Avatar of Vipin Kumar
Vipin KumarFlag for India

asked on

VBA to create lines based on template

I have a MAC and I am trying to write a VBA excel code to create lines based on a template, but i am not able to get the logic how i can achieve the result.


The template is as below. All the ones starting with $ are variables which will have different values on different sheets.

$ACL $LOCLNW $LOCLNW_IPMI01 
$ACL $LOCLNW $LOCLNW_HEV_MGMT01 
$ACL $LOCLNW $LOCLNW_HEN_MGMT01 
$ACL $LOCLNW $LOCLNW_APP_MGMT01 
$ACL $LOCLNW $LOCLNW_STR_MGMT01 
$ACL $LOCLNW $LOCLNW_SRV 
$ACL $LOCLNW $NW_NET 
$ACL $LOCLNW $LOCLNW_CS01 
$ACL $LOCLNW $LOCLNW_CS01_CS_VP
$ACL $LOCLNW $LOCLNW_APP_MGMT_COMSRVRS 
$ACL $LOCLNW $LOCLNW_NAT01 
$ACL $LOCLNW_IPMI01 $LOCLNW_APP_MGMT01 


The values for the variables from one sheet is as below. Different sheets will have different values, but variable names remain same.

$ACL:2500
$LOCLNW:1.1.32.0 0.0.31.255
$LOCLNW_IPMI01:1.1.32.0 0.0.1.255,1.1.38.0 0.0.1.255
$LOCLNW_HV_MGMT01:1.1.34.0 0.0.1.255
$LOCLNW_HANA_MGMT01:1.1.40.0 0.0.1.255
$LOCLNW_APP_MGMT01:1.1.52.0 0.0.3.255,1.1.56.0 0.0.7.255
$LOCLNW_STOR_MGMT01:1.1.46.0 0.0.1.255
$LOCLNW_SERVICES:1.2.128.0 0.0.7.255
$NW_NETSEC:1.1.117.0 0.0.0.255
$LOCLNW_CIS01:1.2.132.0 0.0.1.255
$LOCLNW_CIS01_CIS_VIPS:1.2.133.0 0.0.0.255
$LOCLNW_APP_MGMT_COMSERVERS:1.1.55.128 0.0.0.127,1.1.59.128 0.0.0.127,1.1.63.128 0.0.0.127
$LOCLNW_NAT01:1.2.130.0 0.0.0.255


I want to create a logic so that it create lines as below for ex:

$ACL $LOCLNW $LOCLNW_IPMI01 --> this should come as below, it creates two lines because the variables $LOCLNW_IPMI01 has two values in it separated by comma.

2500 1.1.32.0 0.0.31.255 1.1.32.0 0.0.1.255

2500 1.1.32.0 0.0.31.255 1.1.38.0 0.0.1.255

Avatar of aikimark
aikimark
Flag of United States of America image

Substitution is relatively straight forward, using the VBA Replace() function.

Since you're using a MAC, you'll have to use a 2D array to store the replacement name/value pairs.

If you have a lot of name/value pairs, you might have to do a performance tweak.  But hold off on that until you get it working and do some performance testing.
Avatar of Vipin Kumar

ASKER

Hi aiki,

Yeah i am aware of substitution using the replace function but there are variables which have multiple values stored in them and for that template line it should generate that many lines as many values it has in the variable. If you can show up the logic with basic code how can i achieve that, i can modify it accordingly as per requirement.

For ex:
$ACL $LOCLNW_IPMI01 $LOCLNW_APP_MGMT01 --> this should be end up as below as both $LOCLNW_IPMI01 and $LOCLNW_APP_MGMT01have multiple values in them.
2500 1.1.32.0 0.0.1.255 1.1.52.0 0.0.3.255
2500 1.1.38.0 0.0.1.255 1.1.52.0 0.0.3.255
2500 1.1.32.0 0.0.1.255 1.1.56.0 0.0.7.255
2500 1.1.38.0 0.0.1.255 1.1.56.0 0.0.7.25

i hope you get the point
Will the template lines always have three $ values?
not necessarily they might have one, two three or none
none
Because there are no matches in the substitution table or because there are no $ values in the template string?
here i have only given a sample of few lines... the template list is long... so there are lines in the template which will not have any $ variables... also there are lines which can have one two or three $ variables

for ex:
! this is sample
$ACL
# this is another $ LOCLNW sample
$ ACL $LOCLNW_IPMI01
Please post examples of none and examples where there are non $ values in the template
i have provided the same in my previous comment

the values of $ variables will never be empty it will be just in the template line there can be lines with $variables or none
there are lines in the template which will not have any $ variables
Which of the lines that you've posted do not contain any $ variables?

Which of the lines that you've posted contain text that is not a $ variable?
for ex:
! this is sample --> this doesnt contain any $ variable
$ACL
# this is another $ LOCLNW sample
$ ACL $LOCLNW_IPMI01
Which of the lines that you've posted contain text that is not a $ variable?
Let me clarify...Do you have any lines that contain one, or more, $ variables and some non-substitution text?

Example:
$ACL YADA YADA BR549

Open in new window

yes
I'm encountering an ASCII 160 at the end of each line of the template.  Is that an artifact of our copy/paste operations or are there really 160 characters in your template data?
there is no ascii 160 character
ASKER CERTIFIED SOLUTION
Avatar of aikimark
aikimark
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@Vipin

Are you still testing my code?