Hi there,

I have an issue with one local macro within a fovalues loop.

The main purpose of this code is to look for xlsx files in a directory, open them one at a time (each xlsx is a 'policy'), loop over its sheets (each sheet has data on that policy for a specific country), use both the name of the xlsx and of the sheet to find a corresponding csv, do some manipulations and save a new xlsx named with the name of the original xlsx (the policy) and with sheets with country names (appending sheets to the original xlsx as the loop goes on).

In particular, in fact, the loop seemed to work at the beginning (it displayed all the locals for all sheets within all xlsxs) but when I inserted the commands I need (see the bold comment in the code) at the end of the first iteration it fails to find the local `country' and gives me the error: "file Summary/_Mood.csv not found (Mood is the local `policy')". Any idea about what I did wrong? Please find the code below.

Code:
cd "C:/Users/miche/Opinion Data"
* Get the names of all the xlsx files in the directory (each file refers to a policy area)
local filelist : dir . files "*.xlsx"

* Loop over each file
foreach file in `filelist'{

* Remove file extension to get the name of the policy area
local p = strpos("`file'",".")
local policy = substr("`file'",1,`p'-1)

* Open the xlsx of a specific policy area
import excel using "`policy'", describe

* Within each file (policy), loop over the worksheets (each ws is a country)
forvalues s = 1/`r(N_worksheet)'{
local country "`r(worksheet_`s')'"
di "`policy'"
di "`country'"
import delimited "Summary/`country'_`policy'.csv", delimiter(",") varnames(1) clear
rename v1 _varname
rename v2 obs
rename v3 loading
rename v4 mean
rename v5 sd

* generate actual values of mean and sd to impose on mood, weightig by validity and number of observations
g wt_mean = (loading^2)*mean*obs

* LOOP RUNS SMOOTHLY IF I INCLUDE ONLY WHAT IS ABOVE THIS COMMENT. 
egen denom_mean = total(wt_mean)
g val_obs = (loading^2)*obs
egen numerator = total(val_obs)
g mood_mean = denom_mean/numerator
g wt_sd = (loading^2)*sd*obs
egen denom_sd = total(wt_sd)
g mood_sd = denom_sd/numerator

scalar new_mean = mood_mean[1]
scalar new_sd = mood_sd[1]

drop wt_* denom_* numerator

* Import mood estimates not weighetd for observations
capture noisily import excel using "`policy'.xlsx", sheet("`country'") firstrow clear
if !_rc{
g ym = ym(year, month)

qui sum mood
* Standardize original series
qui replace mood = (mood - `r(mean)')/`r(sd)'

* Impose new mean and sd in order to get mood weighted for item validity and number of observations
qui replace mood = mood*new_sd
qui replace mood = mood + new_mean
scalar drop new_mean new_sd

g yq = qofd(dofm(ym))
collapse (mean) mood, by (yq)
g country = "`country'"
order yq mood

export excel "ObsWeighted/`policy'_ObsW.xlsx", sheet("`country'") firstrow(variables) sheetmodify
}
}
}